Thanks:  0
Likes:  0

# Thread: I'm guessing this is a basic IF formula question...

1. ## I'm guessing this is a basic IF formula question...

If I have a list of names in column A and want to search that column for a subset of those names, and for "true" results generate the text "PCG" in column B of the corresponding row, which formula do I need to enter for column B? I'd like to do this as an array formula for all of column A and include approximately 30 names to search for. Visual example, searching column A for the names "John Smith" "Adam Pierce" and "Lisa Saxon" would generate "PCG" in column B....

 Phil Bauer John Smith PCG Jodi Arlington Adam Pierce PCG Lisa Saxon PCG Joe Fordham

2. ## Re: I'm guessing this is a basic IF formula question...

Hi,

Perhaps you can try something like this:

Excel 2013
ABCD
1Original listResult columnLookup list
3John SmithPGCJohn Smith
4Jodi ArlingtonLisa Saxon
6Lisa SaxonPGC
7Joe Fordham

Sheet1

In B2 and copy down enter the formula:

Code:
```=IFERROR(
IF(A2 = LOOKUP(A2, D\$2:D\$4), "PGC", ""),
"")```
Note, you must ensure the lookup list in column D is sorted in ascending order for this method to work.

An alternative method that doesn't rely on the lookup list being sorted, might be something like this in B2 and copy down:

Code:
`=IF(COUNTIF(D\$2:D\$4, A2) > 0, "PGC", "")`
However this method will be significantly slower with a large list.

3. ## Re: I'm guessing this is a basic IF formula question...

Hello bg61380, welcome to MrExcel

You could use this formula syntax in B2 copied down

or list all of your 30 names somewhere, e.g. in Z1:Z30 and then use that range in the above formula, e.g.

=IF(ISNUMBER(MATCH(A2,Z\$1:Z\$30,0)),"PCG","")

copy formula down column

4. ## Re: I'm guessing this is a basic IF formula question...

Originally Posted by circledchicken
A B C D
1 Original list Result column Lookup list
3 John Smith PGC John Smith
4 Jodi Arlington Lisa Saxon
6 Lisa Saxon PGC
7 Joe Fordham
Note, you must ensure the lookup list in column D is sorted in ascending order for this method to work.

An alternative method that doesn't rely on the lookup list being sorted, might be something like this in B2 and copy down:

Code:
`=IF(COUNTIF(D\$2:D\$4, A2) > 0, "PGC", "")`
However this method will be significantly slower with a large list.
Two questions--when you said "must ensure the lookup list in column D is sorted in ascending order" do you mean alphabetically?

Also, do these formulas require the "Ctrl-Shift-Enter" method of entry to search the whole of Column A and provide responses in the corresponding rows of column B?

5. ## Re: I'm guessing this is a basic IF formula question...

None of the formulas suggested by circledchicken or me require CTRL+SHIFT+ENTER

just put the formula in the first row and copy/fill down the column

6. ## Re: I'm guessing this is a basic IF formula question...

Originally Posted by barry houdini
None of the formulas suggested by circledchicken or me require CTRL+SHIFT+ENTER

just put the formula in the first row and copy/fill down the column
Ha oh boy. Well I wanted to do this as an array formula, so I used

=IFERROR(IF(A2:A7 = LOOKUP(A2:A7, D2:D4), "PCG", ""), "") CTRL+SHIFT+ENTER

And it worked perfectly. Except now I can no longer sort the names alphabetically because it's part of the array. Maybe I should try your way with a "new" formula for each row.

7. ## Re: I'm guessing this is a basic IF formula question...

Originally Posted by bg61380
...Except now I can no longer sort the names alphabetically because it's part of the array. Maybe I should try your way with a "new" formula for each row.
You should still be able to sort the lookup list in column D (the list in column A doesn't need to be sorted).
But in any case, as barry houdini mentioned, you don't need the array structure - its just complicating things.

If for some reason you want a single static formula solution, better options you can try include:
-> using a Data Table, where Excel will automatically populate the result cells with the {=TABLE(,A1)} type syntax
-> use a smart table with the associated structured references, so that your formula would change to a static form something like:
Code:
```=IFERROR(
IF([@[Original list]] = LOOKUP([@[Original list]], D\$2:D\$4), "PGC", ""),
"")```
For more on data tables, see for example:
How to use Microsoft Excel data tables to analyze information in a database
Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide | Chandoo.org - Learn Microsoft Excel Online

For more on smart tables, see for example:
https://office.microsoft.com/en-us/e...010155686.aspx
Working with Tables in Excel 2013, 2010 and 2007

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•