vlookup formula to shows 2nd , 3rd, 4th result for same arrey value.

[Amit Desai]

Namste,

i have table of data where one product code have multiple values. is there any way to lookup for all the available values in column. VLOOKUP is bringing only first match value. for your ready reference i am giving you sample data. if some expert can help me to get my desired result i will be very thankful. ( should support office version 2016 and above )

Book11.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	vlookup formula	expected result
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	350T.31102001284	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	350T.31102001284	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	350T.31102001284	350T.41102001284
18	1102001037	P-1	45447	250T.1	A	250T.11102001037	27812	570	250T.11102001037
19	1102001037	P-1	45447	250T.1	B	250T.11102001037	39254	168	250T.11102001037
20	1102001287	P-1	45447	250T.4	A	250T.41102001287	7199	49	250T.41102001287
Sheet3

Cell Formulas
Range	Formula
I2:I20	I2	=VLOOKUP(A2,$A$2:$F$52,6,0)

 

[arun_eagertolearn]

The simple way is to add two helper column, which has the number of times for which an entry is appearing and concatenating the original entry with this number

Please find below

As the looking happens to the left, INDEX/MATCH would have to be used

Please do let know whether this works

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	Count of Item Code	Concat	Index/Match formula	expected result
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	1	1102001059-1	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	2	1102001059-2	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	1	1102000332-1	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	2	1102000332-2	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	1	1102001433-1	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	1	1102000690-1	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	2	1102000690-2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	1	1102000039-1	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	2	1102000039-2	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	1	1102000363-1	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	2	1102000363-2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	1	1102001440-1	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	2	1102001440-2	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	1	1102001284-1	350T.31102001284	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	2	1102001284-2	350T.21102001284	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	3	1102001284-3	350T.41102001284	350T.41102001284
Sheet2

Cell Formulas
Range	Formula
I2:I17	I2	=COUNTIF(A$2:A2,A2)
J2:J17	J2	=A2&"-"&I2
K2:K17	K2	=INDEX($F$2:$F$17,MATCH(J2,$J$2:$J$17,0))

 

[Cubist]

Have you tried a pivot table?

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	vlookup formula
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	350T.41102001284
18	1102001037	P-1	45447	250T.1	A	250T.11102001037	27812	570	250T.11102001037
19	1102001037	P-1	45447	250T.1	B	250T.11102001037	39254	168	250T.11102001037
20	1102001287	P-1	45447	250T.4	A	250T.41102001287	7199	49	250T.41102001287
Data

Cell Formulas
Range	Formula
I2:I20	I2	=INDEX(F:F,AGGREGATE(15,6,ROW($A$2:$A$100)/($A$2:$A$100=A2),COUNTIFS(A$2:A2,A2)))

 

[Amit Desai]

Have you tried a pivot table?

actually both the data is in different sheet. only common items is "Item Code" that's why i have use vlookup but it gives only first result in all common "Item Code"

 

[Cubist]

actually both the data is in different sheet. only common items is "Item Code" that's why i have use vlookup but it gives only first result in all common "Item Code"

Ok, another option.

Book1
	A	B	C	D	E	F	G	H	I
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	Lookup Formula
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	350T.41102001284
18	1102001037	P-1	45447	250T.1	A	250T.11102001037	27812	570	250T.11102001037
19	1102001037	P-1	45447	250T.1	B	250T.11102001037	39254	168	250T.11102001037
20	1102001287	P-1	45447	250T.4	A	250T.41102001287	7199	49	250T.41102001287
Sheet2

Cell Formulas
Range	Formula
I2:I20	I2	=LOOKUP(2,1/($A$2:A2=A2),$F$2:F2)

 

[Amit Desai]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	vlookup formula
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	350T.41102001284
18	1102001037	P-1	45447	250T.1	A	250T.11102001037	27812	570	250T.11102001037
19	1102001037	P-1	45447	250T.1	B	250T.11102001037	39254	168	250T.11102001037
20	1102001287	P-1	45447	250T.4	A	250T.41102001287	7199	49	250T.41102001287
Data

Cell Formulas
Range	Formula
I2:I20	I2	=INDEX(F:F,AGGREGATE(15,6,ROW($A$2:$A$100)/($A$2:$A$100=A2),COUNTIFS(A$2:A2,A2)))

Thanks for your reply. the formula looks good but what if the "F" column data is actually resides in another sheet name "DPR_Link-DT" G column so what change i have to make to get the desired result?
i have changed your formula like this "=INDEX('DPR_Link-DT'!G:G,AGGREGATE(15,6,ROW($A$2:$A$5100)/($A$2:$A$5100=A414),COUNTIFS(A$2:A414,A414)))" but it didnt worked.

 

[Fluff]

Try

=INDEX('DPR_Link-DT'!G:G,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5100)/('DPR_Link-DT'!$A$2:$A$5100=A414),COUNTIFS('DPR_Link-DT'!A$2:A,A2)))

 

[Amit Desai]

Ok, another option.

Book1
	A	B	C	D	E	F	G	H	I
1	Item Code	Unit	DATE	M/C	Shift	C&A	OK Count	Rej Count	Lookup Formula
2	1102001059	P-1	45447	775T	A	775T1102001059	0	0	775T1102001059
3	1102001059	P-1	45447	775T	B	775T1102001059	0	0	775T1102001059
4	1102000332	P-1	45447	660T.1	A	660T.11102000332	248	3	660T.11102000332
5	1102000332	P-1	45447	660T.1	B	660T.11102000332	321	0	660T.11102000332
6	1102001433	P-1	45447	660T.2	A	660T.21102001433	83	12	660T.21102001433
7	1102000690	P-1	45447	660T.2	A1	660T.21102000690	86	11	660T.21102000690
8	1102000690	P-1	45447	660T.2	B	660T.21102000690	523	2	660T.21102000690
9	1102000039	P-1	45447	450T.1	A	450T.11102000039	98	2	450T.11102000039
10	1102000039	P-1	45447	450T.1	B	450T.11102000039	128	0	450T.11102000039
11	1102000363	P-1	45447	350T.1	A	350T.11102000363	236	2	350T.11102000363
12	1102000363	P-1	45447	350T.1	B	350T.11102000363	344	2	350T.11102000363
13	1102001440	P-1	45447	350T.2	A	350T.21102001440	806	58	350T.21102001440
14	1102001440	P-1	45447	350T.2	B	350T.21102001440	1240	50	350T.21102001440
15	1102001284	P-1	45447	350T.3	A	350T.31102001284	4456	216	350T.31102001284
16	1102001284	P-1	45447	350T.2	B	350T.21102001284	8090	174	350T.21102001284
17	1102001284	P-1	45447	350T.4	B	350T.41102001284	5230	250	350T.41102001284
18	1102001037	P-1	45447	250T.1	A	250T.11102001037	27812	570	250T.11102001037
19	1102001037	P-1	45447	250T.1	B	250T.11102001037	39254	168	250T.11102001037
20	1102001287	P-1	45447	250T.4	A	250T.41102001287	7199	49	250T.41102001287
Sheet2

Cell Formulas
Range	Formula
I2:I20	I2	=LOOKUP(2,1/($A$2:A2=A2),$F$2:F2)

sorry didnt worked , i have changed formula as "=LOOKUP(2,1/($A$2:A414=A414),'DPR_Link-DT'!$G$2:G414)" ( this formula i have written 414 nos row ) so cell refrence showing "A414"

 

[Amit Desai]

Try

=INDEX('DPR_Link-DT'!G:G,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5100)/('DPR_Link-DT'!$A$2:$A$5100=A414),COUNTIFS('DPR_Link-DT'!A$2:A,A2)))

giving name error

Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx
	L	M	N	O	P	Q
1	Product_Matched	Unit	DATE	M/C	Shift	MC+SFG
414	1102000039	P-1	45447	450T.1	A	#NAME?
415	1102000039	P-1	45447	450T.1	A	#NAME?
BOM_Amit-DND

Cell Formulas
Range	Formula
L414:L415	L414	=IFERROR(VLOOKUP($A414,'DPR_Link-DT'!$A$2:$O$995,1,0),"")
M414:M415	M414	=IFERROR(VLOOKUP($A414,'DPR_Link-DT'!$A$2:$O$995,3,0),"")
N414:N415	N414	=IFERROR(VLOOKUP($A414,'DPR_Link-DT'!$A$2:$O$995,4,0),"")
O414:O415	O414	=IFERROR(VLOOKUP($A414,'DPR_Link-DT'!$A$2:$O$995,5,0),"")
P414:P415	P414	=IFERROR(VLOOKUP($A414,'DPR_Link-DT'!$A$2:$O$995,6,0),"")
Q414:Q415	Q414	=INDEX('DPR_Link-DT'!G:G,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5100)/('DPR_Link-DT'!$A$2:$A$5100=A414),COUNTIFS('DPR_Link-DT'!A$2:A,A5414)))