# Lookup & return Multiple Results

1. ## Lookup & return Multiple Results

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.  Register To Reply

2. ## Re: Lookup & return Multiple Results

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  Register To Reply

3. ## Re: Lookup & return Multiple Results

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