Vlookup - Return All matches

G

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?
 
Sort by date Sort by votes
RedJames said:
It's called LMV
Click to expand...

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:
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)

I hope you already correctly done the foregoing.

Go to LMV.

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

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

Hope this clear things up for you.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)
 
Upvote 0
RedJames said:
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)
Click to expand...

An oversight. Lrow should be:
Rich (BB code): 
=MATCH(9.99999999999999E+307,'DARTS offers'!$A:$A)
 
Upvote 0
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.
 
Upvote 0
Nicole Richard said:
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.
Click to expand...

Could you describe the problem you want to solve and post a relevant sample?
 
Upvote 0
Aladin Akyurek said:
Care to elaborate?
Click to expand...

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

[TABLE="width: 451"]
<colgroup><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MAX OF SHIFT[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]34[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]55[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]95[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]46[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]65[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]78[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]35[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]45[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]94[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]55[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
almr3k said:
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

[TABLE="width: 451"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MAX OF SHIFT[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]A[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]B[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/15[/TD]
[TD]C[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Chart Question
Replies
0
Views
167
littleleaguedad
L
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
127
Fluff
Fluff
A
  • Solved
Return values from multiple columns based on a list
Replies
6
Views
47
LegendaryAxe
L
R
  • Solved
Using Countif with Match or something similar?
Replies
5
Views
236
Fluff
Fluff
C
  • Solved
Formula using match, indirect, and vlookup returning 0
Replies
1
Views
323
Ccruz444
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

Share this page

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