Vlookup Multiple Matches

R

RoadKill

Okay, here is my current formula: =VLOOKUP(B$3, Sups!$A2:$D$1000,2,0)

The problem is that B3 may appear up to 20 times and vlookup only pulls the
first match. But I want it to pull every instance when I drag the fomula
down. To elaborate, B5 of the current sheet would display the first instance,
B6 the second and on down the line.

I seem to remember doing it before but cannnot recall whether is was using
match, etc.

Thanks for your help.
 
T

T. Valko

Try this array formula** :

=IF(ROWS(B$5:B5)<=COUNTIF(Sups!A$2:A$1000,B$3),INDEX(Sups!B$2:B$1000,SMALL(IF(Sups!A$2:A$1000=B$3,ROW(Sups!B$2:B$1000)),ROWS(B$5:B5))-MIN(ROW(Sups!B$2:B$1000))+1),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
R

RoadKill

Beautiful. Thank you

T. Valko said:
Try this array formula** :

=IF(ROWS(B$5:B5)<=COUNTIF(Sups!A$2:A$1000,B$3),INDEX(Sups!B$2:B$1000,SMALL(IF(Sups!A$2:A$1000=B$3,ROW(Sups!B$2:B$1000)),ROWS(B$5:B5))-MIN(ROW(Sups!B$2:B$1000))+1),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top