Vlookup to return 2nd, 3rd or 4th matching value

Yossi

New Member
Joined
Aug 7, 2012
Messages
44
Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second or third values in the Formula result?

In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.

I'm using Excel 2010
the empty boxes is where i need the results
ABCDE
1Item NumberPO DateQtyOrderedQtyRecdQtyOpen
22-468138/17/2012800,000.00184,800.00615,200.001st qty1st date2nd qty2nd date3rd qty3rd date4th qty4th date
32-68543110/19/2012390,000.00388,080.001,920.002-46813
44-4638413547/24/201322,880.0012,936.009,944.002-685431
53-468437/24/20131,188.001,155.0033.004-463841354
63-46852558/26/20135,000.004,600.00400.003-46843
74-13841310/4/2013900.00300.00600.003-4685255
88-16854.310/4/20132,800.001,480.001,320.004-138413
95-57438410/4/2013800.00300.00500.008-16854.3
107-18434144510/16/20131,550.000.001,550.005-574384
111-68435410/16/20131,100.000.001,100.007-184341445
128-4683843810/16/2013140.000.00140.001-684354
136-648343810/16/20138,100.000.008,100.008-46838438
142-4681310/17/2013350,000.000.00350,000.006-6483438
152-68543110/17/2013725,000.000.00725,000.002-46813
164-46384135410/18/20132,430.001,160.001,270.00
173-4684310/18/20135,034.002,803.002,231.00
183-468525510/18/20135,880.005,800.0080.00
194-13841310/22/20132,700.000.002,700.00
208-16854.310/23/2013299,970.0099,990.00199,980.00
215-57438410/23/2013299,700.0099,900.00199,800.00
227-18434144510/23/20132,380.000.002,380.00
231-68435410/23/201320,000.00765.0019,235.00
248-4683843810/23/201316,666.004,752.0011,914.00
256-648343810/24/2013124.000.00124.00
262-4681310/24/201381.000.0081.00
272-68543110/24/201391.000.0091.00
284-46384135410/24/2013236.000.00236.00
293-4684310/24/201320,000.000.0020,000.00
303-468525510/25/20132,840.000.002,840.00
314-13841310/25/20131,240.000.001,240.00
328-16854.310/25/20138,366.004,320.004,046.00
335-57438410/25/2013500.000.00500.00
347-18434144510/25/20131,000.000.001,000.00
351-68435410/25/20132,500.000.002,500.00
368-4683843810/25/201310,300.000.0010,300.00
376-648343810/25/201310,000.000.0010,000.00

<tbody>
</tbody>

thank you so much for all your help
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
You could do this with index...

If an if statement to return the row # of item #s that match... then use SMALL() to return the kth smallest row. Your result table isn't setup quite how I'd do it, but here is a formula to get you started...

=INDEX($B$2:$B$37,SMALL(IF($A$2:$A$37=$G3,ROW($B$2:$B$37)-1,""),1))
Array formula, confirm with crtl + shift + enter
 

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
As far as I know it can not be done with Vlookups, however

B3 =IFERROR(INDEX(Lookup_values!$A$2:$C$10000,SMALL(IF(Lookup_values!$A$2:$A$10000=Table_arrays!$A3,ROW(Lookup_values!$A$2:$A$10000)-1),Column(A1)),3),"")

C3 =IFERROR(INDEX(Lookup_values!$A$2:$C$10000,SMALL(IF(Lookup_values!$A$2:$A$10000=Table_arrays!$A3,ROW(Lookup_values!$A$2:$A$10000)-1),Column(A1)),2),"")

Confirmed with Ctrl+Shift+Enter instead of just Enter

I've honestly never tried to grab both these formulas and drag right, but it might work.

if not just change the Column(A1) to Column(B1) and Column(C1) to change the occurence
 

Yossi

New Member
Joined
Aug 7, 2012
Messages
44
its not working cause its not doing a vlookup
and the item numbers which is in column A is not allways aligned so i need to get a vlookup in there some how
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
its not working cause its not doing a vlookup
and the item numbers which is in column A is not allways aligned so i need to get a vlookup in there some how

Index will act as a vlookup. Index is more flexible than vlookup. Give it a shot.
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
Excel 2010
GHIJKLM
21st qty1st date2nd qty2nd date3rd qty3rd date
32-468138000008/17/201235000010/17/20138110/24/2013
42-68543139000010/17/201372500010/17/20139110/24/2013
54-4638413542288010/18/2013243010/18/201323610/24/2013
63-46843118810/18/2013503410/18/20132000010/24/2013
73-4685255500010/18/2013588010/18/2013284010/25/2013
84-13841390010/22/2013270010/22/2013124010/25/2013
98-16854.3280010/23/201329997010/23/2013836610/25/2013
105-57438480010/23/201329970010/23/201350010/25/2013
117-184341445155010/23/2013238010/23/2013100010/25/2013
121-684354110010/23/20132000010/23/2013250010/25/2013
138-4683843814010/23/20131666610/23/20131030010/25/2013
146-6483438810010/24/201312410/24/20131000010/25/2013
152-4681380000010/17/201335000010/17/20138110/24/2013

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
H3{=IFERROR(INDEX($C$2:$C$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(A2))),"")}
I3{=IFERROR(INDEX($B$2:$B$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(A2))),"")}
J3{=IFERROR(INDEX($C$2:$C$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(B2))),"")}
K3{=IFERROR(INDEX($B$2:$B$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(B2))),"")}
L3{=IFERROR(INDEX($C$2:$C$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(C2))),"")}
M3{=IFERROR(INDEX($B$2:$B$37,SMALL(IF($G3=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(C2))),"")}
H4{=IFERROR(INDEX($C$2:$C$37,SMALL(IF($G4=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(A3))),"")}
I4{=IFERROR(INDEX($B$2:$B$37,SMALL(IF($G4=$A$2:$A$37,ROW($A$2:$A$37)-ROW($A$2)+1),COLUMN(B3))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top