# Lookup & return Multiple Results

• 07-10-2012, 10:34 AM
benishiryo
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.
• 07-10-2012, 01:22 PM
ChemistB
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
• 07-11-2012, 10:11 AM
benishiryo
Re: Lookup & return Multiple Results
using the COUNTIF at the start is pretty cool. shall use that next time if someone asks. thanks, ChemistB!