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?
 
Aha, that worked! Thanks so much! It was the values in Column A being formatted as text. I also tried to include (ISERROR to keep from getting the #NUM's from showing up in the cells were no more records were found, but it is telling me that I have too many arguments. Am I out of luck on that, or is there another way?
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The formula is constructed in such a way so that it shouldn't return #NUM!, and so there's no need to use ISERROR. Maybe there are still some numbers that are being recognized as text values. Just to be sure, try the following...

1) Select an empty cell.

2) Click on Copy.

3) Select all the numbers in Column A.

4) Paste Special > Add > OK

Does this help?
 
Last edited:
Upvote 0
If all of the numbers are being recognized as numerical values, and if the data is laid out as you've described, and if the formulas are enter in the locations that you've specified, I see no reason why the formula should return #NUM!. If you'd like, I can send you a sample file. If so, send me your email address by Private Message.
 
Upvote 0
Thanks Domenic. That really helped. I am trying to take your advice in replacing the column references with dynamic name ranges. I have creaded the dynamic name ranges using your link an opted for the INDEX and MATCH option. However, I can't get the formula to work. Can you (or anyone else) tell me how to refernce the DNR in the formula?

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

When I put it in like that it is telling me I have created a circular reference. Thanks!
 
Upvote 0
Thanks Domenic. That really helped. I am trying to take your advice in replacing the column references with dynamic name ranges. I have creaded the dynamic name ranges using your link an opted for the INDEX and MATCH option. However, I can't get the formula to work. Can you (or anyone else) tell me how to refernce the DNR in the formula?

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

When I put it in like that it is telling me I have created a circular reference. Thanks!

Care to post the working formula with unnamed ranges?
 
Upvote 0
=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))),"")

Both were applied using CTRL + SHIFT + ENTER
 
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