Vlookup Multiple Matches
 

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.

Vlookup Multiple Matches
 

Try this array formula** :

=IF(ROWS(B$5:B5)<=COUNTIF(Sups!A$2:A$1000,B$3),IND EX(Sups!B$2:B$1000,SMALL(IF(Sups!A$2:A$1000=B$3,RO W(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.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
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.

Vlookup Multiple Matches
 

Beautiful. Thank you

"T. Valko" wrote:

Try this array formula** :

=IF(ROWS(B$5:B5)<=COUNTIF(Sups!A$2:A$1000,B$3),IND EX(Sups!B$2:B$1000,SMALL(IF(Sups!A$2:A$1000=B$3,RO W(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.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
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.

Vlookup Multiple Matches
 

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RoadKill" wrote in message
...
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.

Vlookup Multiple Matches
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
Beautiful. Thank you

"T. Valko" wrote:

Try this array formula** :

=IF(ROWS(B$5:B5)<=COUNTIF(Sups!A$2:A$1000,B$3),IND EX(Sups!B$2:B$1000,SMALL(IF(Sups!A$2:A$1000=B$3,RO W(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.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
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.