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

bg61380

New Member
Joined
Jan 24, 2014
Messages
3
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 SmithPCG
Jodi Arlington
Adam PiercePCG
Lisa SaxonPCG
Joe Fordham

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

Perhaps you can try something like this:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Original list</td><td style="font-weight: bold;;">Result column</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Lookup list</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Phil Bauer</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Adam Pierce</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">John Smith</td><td style=";">PGC</td><td style="text-align: right;;"></td><td style=";">John Smith</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jodi Arlington</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Lisa Saxon</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Adam Pierce</td><td style=";">PGC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Lisa Saxon</td><td style=";">PGC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Joe Fordham</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello bg61380, welcome to MrExcel

You could use this formula syntax in B2 copied down

=IF(ISNUMBER(MATCH(A2,{"John Smith","Adam Pierce","Lisa Saxon"},0)),"PCG","")

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
 

bg61380

New Member
Joined
Jan 24, 2014
Messages
3
ABCD
1Original listResult columnLookup list
2Phil BauerAdam Pierce
3John SmithPGCJohn Smith
4Jodi ArlingtonLisa Saxon
5Adam PiercePGC
6Lisa SaxonPGC
7Joe Fordham

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

bg61380

New Member
Joined
Jan 24, 2014
Messages
3
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.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
...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/...references-with-excel-tables-HA010155686.aspx
Working with Tables in Excel 2013, 2010 and 2007
 

Watch MrExcel Video

Forum statistics

Threads
1,095,881
Messages
5,447,035
Members
405,429
Latest member
vickky9218

This Week's Hot Topics

Top