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

[bg61380]

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....

[TABLE="width: 500"]
<tbody>[TR]
[TD]Phil Bauer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]PCG[/TD]
[/TR]
[TR]
[TD]Jodi Arlington[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Pierce[/TD]
[TD]PCG[/TD]
[/TR]
[TR]
[TD]Lisa Saxon[/TD]
[TD]PCG[/TD]
[/TR]
[TR]
[TD]Joe Fordham[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[circledchicken]

Hi,

Perhaps you can try something like this:

Excel 2013
	A	B	C	D
1	Original list	Result column		Lookup list
2	Phil Bauer			Adam Pierce
3	John Smith	PGC		John Smith
4	Jodi Arlington			Lisa Saxon
5	Adam Pierce	PGC
6	Lisa Saxon	PGC
7	Joe Fordham
Sheet1

In B2 and copy down enter the formula:

=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:

=IF(COUNTIF(D$2:D$4, A2) > 0, "PGC", "")

However this method will be significantly slower with a large list.

 

[barry houdini]

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]

	A	B	C	D
Original list	Result column	Lookup list
Phil Bauer		Adam Pierce
John Smith	PGC	John Smith
Jodi Arlington		Lisa Saxon
Adam Pierce	PGC
Lisa Saxon	PGC
Joe Fordham

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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:

=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]

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]

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]

...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:

=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