Vlookup - Return All matches

[gizzylover]

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?

 

[RedJames]

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?

 

[Domenic]

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?

 

[RedJames]

No, it is still giving me #NUM! for the empty results

 

[Domenic]

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.

 

[RedJames]

That would be great. Thanks Domenic. I am sending now.

 

[Domenic]

Sample workbook sent...

 

[RedJames]

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!

 

[Aladin Akyurek]

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?

 

[RedJames]

=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

 

[Aladin Akyurek]

=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

Which column houses true numbers?