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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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

ADVERTISEMENT

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,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top