Vlookup - Return All matches

gizzylover

Board Regular
Joined
Nov 9, 2005
Messages
55
I've tried searching the boards for Vlookup questions, but I'm not smart enough to figure out to modify previous postings to my current need - so appreciate your patience....

If I want to return ALL values found for a Vlookup (instead of first one found), how do I do that?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Which column houses true numbers?


Go to DARTS offers.
Activate Formulas | Name Manager.
Activate the New tab.
Enter Lrow in the Name box.
Enter the following in the Refers to box:
Rich (BB code):
=MATCH(9.99999999999999E+307,'DARTS offers'!A:A)
Click OK.

Define Arange using the foregoing procedure as referring to:
Rich (BB code):
='DARTS offers'!$A$2:INDEX('DARTS offers'!$A:$A,Lrow)
Define Brange using the foregoing procedure as referring to:
Rich (BB code):
='DARTS offers'!$B$2:INDEX('DARTS offers'!$B:$B,Lrow)

Go to the results sheet which houses the formula of interest.

D3, just enter:
Rich (BB code):
=COUNTIF(Brange,B3)

E3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS(E$3:E3)<=$D$3,INDEX(Brange,SMALL(IF(Arange=$B$3,
  ROW(Brange)-ROW(INDEX(Brange,1,1))+1),ROWS(E$3:E3))),"")
 
Upvote 0
Thanks. I tried all of that, but I am getting #N/A in both D3 and E. It seemed like D3 should have been Arange in stead of Brange so I tried that also with the same result.
 
Upvote 0
Thanks. I tried all of that, but I am getting #N/A in both D3 and E. It seemed like D3 should have been Arange in stead of Brange so I tried that also with the same result.

Indeed, the formula in D3 should refer to Arange.

The formula you posted at my request:

=IF(ROWS(E$3:E3)<=$D$3,INDEX('DARTS offers'!B:B,SMALL(IF('DARTS offers'!A:A=$B$3,ROW('DARTS offers'!B:B)-MIN(ROW('DARTS offers'!B:B))+1),ROWS(E$3:E3))),"")

tells me the following.

1. You have data in DARTS offers in column A and B.

2. You have a destination sheet for the results you want to see.

3. B3 of the destination sheet houses a condition/criterion that must hold for column A of DARTS offers.

4. D3 of the destination sheet houses a COUNTIF formula.

5. The above quoted formula in control+shift+entered in E3 of the destination sheet and copied down.

6. You stated at my request that column A of DARTS offers is numeric.

We have defined dynamic name ranges using the foregoing, which adjust automatically whenever DARTS offers changes.

The formula

=COUNTIF(Arange,B3)

implemented in D3, along with the formula using dynamic name ranges:

=IF(ROWS(E$3:E3)<=$D$3,INDEX(Brange,SMALL(IF(Arange=$B$3,ROW(Brange)-ROW(INDEX(Brange,1,1))+1),ROWS(E$3:E3))),"")

implemented in E3 (using control+shift+enter) of DARTS offers, should deliver exactly the same results as the formula with whole column references you already tried out.
 
Upvote 0
It seems that D3 is the issue since I am getting the #N/A error there. Perhaps the named range code is not right? Can you verify if this seems correct?

Rich (BB code):
='DARTS offers'!$A$2:INDEX('DARTS offers'!$A:$A,Lrow)
 
Upvote 0
It seems that D3 is the issue since I am getting the #N/A error there. Perhaps the named range code is not right? Can you verify if this seems correct?

Rich (BB code):
='DARTS offers'!$A$2:INDEX('DARTS offers'!$A:$A,Lrow)

The name of the sheet where you have the data is DARTS offers, right?

What is the name of the sheet where you collect the results?
 
Upvote 0
Does it matter that I have designated Lrow using your code which is for true numbers, but column B reports FALSE using ISNUMBER? I'm not sure why that would affect the code in D3, but Lrow is also included in the code for Brange.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top