Vlookup to retun multiple values.

**prasjohn** · 06-19-2010, 02:19 PM

Can I use in any way the vlookup function and return all matches if there are multiple values? I mean, it should return the values of all the matches, not just the first one...If VBA is the only way can anybody help me to get a code ? Thanks !

**contaminated** · 06-19-2010, 02:28 PM

This is possible to do, but not with VLOOKUP. With this kinda formula you can

=INDEX(ReturnRange,SMALL(IF(SearchValue=ValuesRange,ROW(INDIRECT("1:"&ROWS(ValuesRange)))),ROW(A1)))

Confirm it with Ctrl+Shift+Enter and copy down.
Here used named ranges.

SearchValue is J1
ValuesRange = A1:A50
ReturnRange = B1:B50

Also, if you copy this formula down in 15 cell but you have only 7 matches, all other cell will be filled with #NUM! errors.
To avoid this error"
If you use 2007 ucan wrap this formula in IFERROR function... ie

=IFERROR(Formula,"")

If you use earlier version of excel you can wrap it into:
1: =IF(ISERROR(Formula),"",Formula)
2: =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",Formula) ' This will work if ALL your outputs are TEXT
3: =LOOKUP(99^99,CHOOSE({1,2},0,Formula) ' This will work if ALL your outputs are NUMBERS