Vlookup Multiple Matches

[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. 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.

 

[RoadKill]

Beautiful. Thank you

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.

 

[Ashish Mathur]

Hi,

You can also refer to my article at -
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[itwas45and51]

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!

 

[itwas45and51]

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.

 

[Anon_F]

always good to see 3+ year old threads recycled!