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]

A

 

[Aladin Akyurek]

Which column houses true numbers?

A

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:

=MATCH(9.99999999999999E+307,'DARTS offers'!A:A)

Click OK.

Define Arange using the foregoing procedure as referring to:

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

Define Brange using the foregoing procedure as referring to:

='DARTS offers'!$B$2:INDEX('DARTS offers'!$B:$B,Lrow)

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

D3, just enter:

=COUNTIF(Brange,B3)

E3, control+shift+enter, not just enter, and copy down:

=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))),"")

 

[RedJames]

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.

 

[Aladin Akyurek]

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.

 

[RedJames]

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?

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

 

[Aladin Akyurek]

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?

='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?

 

[RedJames]

Yes, it is also Sheet13 which I tried to substitute to no avail.

 

[Aladin Akyurek]

Yes, it is also Sheet13 which I tried to substitute to no avail.

DARTS offers is the data sheet.

But what is the results sheet, that is, the sheet where you want to see results?

 

[RedJames]

It's called LMV

 

[RedJames]

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.