Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and multiple matches | Excel Discussion (Misc queries) | |||
Can vlookup be used to retrieve multiple matches | Excel Discussion (Misc queries) | |||
Vlookup to return the sum of multiple matches | Excel Discussion (Misc queries) | |||
How do I get multiple matches when using the Vlookup function? | Excel Worksheet Functions | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions |