Lookup in Multiple Columns, Return Multiple Values

[andy62]

I hope it is okay to repost this question; my original post seemed hard to
follow and poorly titled.

I need to do a lookup that can find all instances of a value in multiple
columns of an array, and then return all the values in the "A" column of that
array of rows found to contain that value. For example, see the following
array:

A B C D
1 Pres Sam Dot Ed
2 SVP Dot Ed Bill
3 VP Ed Sam Sue
4 Dir Sue Dot Ron

The lookup, when told to find "Sam", would return the following array of
data (the values in column A of any rows containing "Sam"):
Pres
VP

If told to find "Dot", the result would be:
SVP
Dir

So I think the formula should be entered as an array, so that one formula
would return all the needed results.

If necessary, I can move or copy column A to the right side of the array.

Thanks in advance, this one has me stumped!

 

[Domenic]

Assumptions:

A25 contains the data

F2 contains the lookup value, such as Sam

There will only be one instance of the lookup value, such as 'Sam', in
any row

Formula:

G2, copied down:

=IF(ROWS(G$2:G2)<=COUNTIF($B$2:$D$5,$F$2),INDEX(A$2:A$5,SMALL(IF($B$2:$D$
5=$F$2,ROW($B$2:$D$5)-ROW($B$2)+1),ROWS(G$2:G2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[JMB]

One way, array entered (using Control+Shift+Enter) - if your data was in
A14, F1 contained the name you were looking for, and the formula is entered
into H1, then:

=INDEX($A$1:$A$4,SMALL(IF(COUNTIF(INDIRECT("B"&ROW(INDIRECT(ROW($B$1)&":"&ROW($B$4)))&""&ROW(INDIRECT(ROW($D$1)&":"&ROW($D$4)))),$F$1),ROW(INDIRECT("1:"&ROWS($A$1:$A$4))),""),ROW()-ROW(H$1)+1))

copy down until you get #NUM.

Change ranges depending on where your data is.

 

[andy62]

Thank you, that worked! Not that I know how . . . .