Lookup in Multiple Columns, Return Multiple Values

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!

Assumptions:

A2:D5 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!

In article <[email protected]>,
andy62 <[email protected]> wrote:

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

One way, array entered (using Control+Shift+Enter) - if your data was in
A1:D4, 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)))&":D"&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" wrote:

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

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

"Domenic" wrote:

> Assumptions:
>
> A2:D5 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!
>
> In article <[email protected]>,
> andy62 <[email protected]> wrote:
>
> > 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!
>