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

[Yossi]

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

	A	B	C	D	E
1	Item Number	PO Date	QtyOrdered	QtyRecd	QtyOpen
2	2-46813	8/17/2012	800,000.00	184,800.00	615,200.00			1st qty	1st date	2nd qty	2nd date	3rd qty	3rd date	4th qty	4th date
3	2-685431	10/19/2012	390,000.00	388,080.00	1,920.00		2-46813
4	4-463841354	7/24/2013	22,880.00	12,936.00	9,944.00		2-685431
5	3-46843	7/24/2013	1,188.00	1,155.00	33.00		4-463841354
6	3-4685255	8/26/2013	5,000.00	4,600.00	400.00		3-46843
7	4-138413	10/4/2013	900.00	300.00	600.00		3-4685255
8	8-16854.3	10/4/2013	2,800.00	1,480.00	1,320.00		4-138413
9	5-574384	10/4/2013	800.00	300.00	500.00		8-16854.3
10	7-184341445	10/16/2013	1,550.00	0.00	1,550.00		5-574384
11	1-684354	10/16/2013	1,100.00	0.00	1,100.00		7-184341445
12	8-46838438	10/16/2013	140.00	0.00	140.00		1-684354
13	6-6483438	10/16/2013	8,100.00	0.00	8,100.00		8-46838438
14	2-46813	10/17/2013	350,000.00	0.00	350,000.00		6-6483438
15	2-685431	10/17/2013	725,000.00	0.00	725,000.00		2-46813
16	4-463841354	10/18/2013	2,430.00	1,160.00	1,270.00
17	3-46843	10/18/2013	5,034.00	2,803.00	2,231.00
18	3-4685255	10/18/2013	5,880.00	5,800.00	80.00
19	4-138413	10/22/2013	2,700.00	0.00	2,700.00
20	8-16854.3	10/23/2013	299,970.00	99,990.00	199,980.00
21	5-574384	10/23/2013	299,700.00	99,900.00	199,800.00
22	7-184341445	10/23/2013	2,380.00	0.00	2,380.00
23	1-684354	10/23/2013	20,000.00	765.00	19,235.00
24	8-46838438	10/23/2013	16,666.00	4,752.00	11,914.00
25	6-6483438	10/24/2013	124.00	0.00	124.00
26	2-46813	10/24/2013	81.00	0.00	81.00
27	2-685431	10/24/2013	91.00	0.00	91.00
28	4-463841354	10/24/2013	236.00	0.00	236.00
29	3-46843	10/24/2013	20,000.00	0.00	20,000.00
30	3-4685255	10/25/2013	2,840.00	0.00	2,840.00
31	4-138413	10/25/2013	1,240.00	0.00	1,240.00
32	8-16854.3	10/25/2013	8,366.00	4,320.00	4,046.00
33	5-574384	10/25/2013	500.00	0.00	500.00
34	7-184341445	10/25/2013	1,000.00	0.00	1,000.00
35	1-684354	10/25/2013	2,500.00	0.00	2,500.00
36	8-46838438	10/25/2013	10,300.00	0.00	10,300.00
37	6-6483438	10/25/2013	10,000.00	0.00	10,000.00

<tbody>
</tbody>

thank you so much for all your help

 

[pplstuff]

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]

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]

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]

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]

Excel 2010

	G	H	I	J	K	L	M
2		1st qty	1st date	2nd qty	2nd date	3rd qty	3rd date
3	2-46813	800000	8/17/2012	350000	10/17/2013	81	10/24/2013
4	2-685431	390000	10/17/2013	725000	10/17/2013	91	10/24/2013
5	4-463841354	22880	10/18/2013	2430	10/18/2013	236	10/24/2013
6	3-46843	1188	10/18/2013	5034	10/18/2013	20000	10/24/2013
7	3-4685255	5000	10/18/2013	5880	10/18/2013	2840	10/25/2013
8	4-138413	900	10/22/2013	2700	10/22/2013	1240	10/25/2013
9	8-16854.3	2800	10/23/2013	299970	10/23/2013	8366	10/25/2013
10	5-574384	800	10/23/2013	299700	10/23/2013	500	10/25/2013
11	7-184341445	1550	10/23/2013	2380	10/23/2013	1000	10/25/2013
12	1-684354	1100	10/23/2013	20000	10/23/2013	2500	10/25/2013
13	8-46838438	140	10/23/2013	16666	10/23/2013	10300	10/25/2013
14	6-6483438	8100	10/24/2013	124	10/24/2013	10000	10/25/2013
15	2-46813	800000	10/17/2013	350000	10/17/2013	81	10/24/2013

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

Sheet2​

Array Formulas Cell Formula 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>