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?

 

[Aladin Akyurek]

It's called LMV

What follows is a repeat of post #42 with LMV incorporated...


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)

I hope you already correctly done the foregoing.

Go to LMV.

B3 houses an item 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))),"")

You can of course opt for cells other than B3, D3, and E3.

Hope this clear things up for you.

 

[RedJames]

I'm not seeing what changed there? I was already entering the code on LMV page in D3 and E3. One thing I did notice was that under Name Manager, the code for Lrow changes at the end after I enter it. !A:A) changes to !XFC:XFC)

 

[Aladin Akyurek]

I'm not seeing what changed there? I was already entering the code on LMV page in D3 and E3. One thing I did notice was that under Name Manager, the code for Lrow changes at the end after I enter it. !A:A) changes to !XFC:XFC)

An oversight. Lrow should be:

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

 

[RedJames]

Aha, that worked! Thanks!!!!!

 

[Aladin Akyurek]

Aha, that worked! Thanks!!!!!

Happy to hear that! You are welcome.

 

[Nicole Richard]

I can't even figure out this table; it says I can click to see formulas, but I don't. Surely I'm missing something. I tried to post but could not use insert excel chart feature from the menu items. Frustration - just trying to use this site.

 

[Aladin Akyurek]

I can't even figure out this table; it says I can click to see formulas, but I don't. Surely I'm missing something. I tried to post but could not use insert excel chart feature from the menu items. Frustration - just trying to use this site.

Could you describe the problem you want to solve and post a relevant sample?

 

[almr3k]

Care to elaborate?

i have a lot of data shiftwise production in one sheet
i want to get maximum number produced in each shift in another sheet could you please help

							MAX OF SHIFT
30/11/15	A	25			30/11/15	A	95
30/11/15	A	34			30/11/15	B	78
30/11/15	A	55			30/11/15	C	94
30/11/15	A	26
30/11/15	A	95
30/11/15	B	46
30/11/15	B	65
30/11/15	B	25
30/11/15	B	78
30/11/15	C	35
30/11/15	C	45
30/11/15	C	15
30/11/15	C	94
30/11/15	C	55

<colgroup><col><col><col><col span="4"><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

i have a lot of data shiftwise production in one sheet
i want to get maximum number produced in each shift in another sheet could you please help

							MAX OF SHIFT
30/11/15	A	25			30/11/15	A	95
30/11/15	A	34			30/11/15	B	78
30/11/15	A	55			30/11/15	C	94
30/11/15	A	26
30/11/15	A	95
30/11/15	B	46
30/11/15	B	65
30/11/15	B	25
30/11/15	B	78
30/11/15	C	35
30/11/15	C	45
30/11/15	C	15
30/11/15	C	94
30/11/15	C	55

<tbody>
</tbody>

Are the pairs, that is, date and shift conbinations, given in the destination sheet? If so, we just need to determine the corresponding max value...