Vlookup Multiple Matches

  • Thread starter Thread starter RoadKill
  • Start date Start date
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.
 
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.
 
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.
Click to expand...
 
I have a similar problem looking up multiple matches. I used the formula from Ashish Mathur's article, but I am getting an incorrect match - one row below the intended result. Can anyone explain why and how to solve? Thanks for your help!
 
Well, I determined a solution, but I am not sure why I had to do it. The formula I employed was =INDEX('stop cause list'!$A$2:$F$1494,SMALL(IF('stop cause list'!$A$2:$F$1494=$G$4,ROW('stop cause list'!$A$2:$F$1494)),ROW(1:1)),6). To get the correct row, I had to change the formula to =INDEX('stop cause list'!$A$2:$F$1494,SMALL(IF('stop cause list'!$A$2:$F$1494=$G$4,ROW('stop cause list'!$A$2:$F$1494)-1),ROW(1:1)),6). ANy help with why that was needed? Thanks again.
 
Back
Top