Vlookup or other formula that can search and return multiple results?

[andrew_milonavic]

Hi,

Is there a way to have a formula (not VBA) search for “X” and return multiple results?

I made a sample with fake data to illustrate what I’m looking for.

[TABLE="class: grid, width: 500"]
<colgroup><col style="width:51pt" width="69" span="9"> </colgroup><tbody>[TR]
[TD="width: 69"] [/TD]
[TD="width: 69"]
[/TD]
[TD="width: 69"]Cats
[/TD]
[TD="width: 69"]Dogs [/TD]
[TD="width: 69"]Sheep[/TD]
[TD="width: 69"]Snakes[/TD]
[TD="width: 69"]insects[/TD]
[TD="width: 69"] [/TD]
[TD="width: 69"]Results:[/TD]
[/TR]
[TR]
[TD]Farm[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Cats
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sheep[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Snakes[/TD]
[/TR]
</tbody>[/TABLE]


In my example, the formula would search 5 cells (horizontally) and the headings from the cells marked with an X would be returned as a result.
Hope this makes sense.

Thanks

Andrew

 

[Scott T]

[TABLE="class: grid, width: 512"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Cats
[/TD]
[TD="width: 64, bgcolor: transparent"]Dogs
[/TD]
[TD="width: 64, bgcolor: transparent"]Sheep
[/TD]
[TD="width: 64, bgcolor: transparent"]Snakes
[/TD]
[TD="width: 64, bgcolor: transparent"]insects
[/TD]
[TD="width: 64, bgcolor: transparent"]Results:
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Farm
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Cats
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Sheep
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Snakes
[/TD]
[/TR]
</tbody>[/TABLE]
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the fourmula. copy down

=IF(ROWS($H$2:H2)>COUNTIF($C$2:$G$2,"x"),"",INDEX($C$1:$G$1,SMALL(IF($C$2:$G$2="x",COLUMN($C$2:$G$2)-COLUMN($C$2)+1),ROWS($H$2:H2))))

 

[andrew_milonavic]

Thanks Scott!!!

Works perfectly. I really appreciate the help.

 

[andrew_milonavic]

Hey Scott,

Another quick question if you don't mind. If I want the results to be presented horizontally on the same row, in columns H,I,J how would the formula be changed?

thanks

Andrew

 

[Scott T]

Just change ROWS to COLUMNS and copy across. Still must use Ctrl+Shift+Enter

=IF([COLOR=#FF0000]COLUMNS[/COLOR]($H$2:H2)>COUNTIF($C$2:$G$2,"x"),"",INDEX($C$1:$G$1,SMALL(IF($C$2:$G$2="x",COLUMN($C$2:$G$2)-COLUMN($C$2)+1),[COLOR=#FF0000]COLUMNS[/COLOR]($H$2:H2))))

 

[andrew_milonavic]

Thanks Scott!!!