Lookup & return Multiple Results

[benishiryo]

awhile back, I started compiling some tricks I learned. So this would probably come in handy here! Here's one that lots of people are asking in this forum. I picked up the original formula here in http://www.get-digital-help.com/2009...okup-in-excel/ & tweaked it a little to be able to exclude errors & copied over to other columns & rows.

Basically, to use this formula to your own scenario, change C$6:$C$12 to the range of the multiple results you want to show. And all $B$6:$B$12 to the lookup range. After that, press CTRL + SHIFT + ENTER to confirm the formula.

[ChemistB]

One of my favorites. I would suggest for Excel 2003 and below

=IF(COUNTIF($B$6:$B$12,$E6)<COLUMN(A1),"",INDEX($C$6:$C$12,SMALL(IF($E6=$B$6:$B$12,ROW($B$6:$B$12)-MIN(ROW($B$6:$B$12))+1,""),COLUMN(A1))))
entered as an array

and for 2007 and up
=IFERROR(INDEX($C$6:$C$12,SMALL(IF($E6=$B$6:$B$12,ROW($B$6:$B$12)-MIN(ROW($B$6:$B$12))+1,""),COLUMN(A1))),"") also as an array

[benishiryo]

using the COUNTIF at the start is pretty cool. shall use that next time if someone asks. thanks, ChemistB!