Vlookup

[Zubair]

Hi all,

Vlookup is bringing 1st line data in column B from column F, how can I bring 2nd & 3rd line data if order number is same?

Book1
	A	B	C	D	E	F	G
1	Finance				Sales
2	Account	Order No.	Reference No.		Account	Order No.	Reference No.
3	4300	1119	AB12		4300	1119	AB12
4	4300	1119	AB12		4300	1120	AB13
5	4301	1130	AB14		4301	1130	AB14
6	4302	1131	AB15		4302	1131	AB15
7	4302	1131	AB15		4302	1132	AB16
8	4302	1131	AB15		4302	1133	AB17
9	4302	1131	AB15		4302	1134	AB18
Sheet1

Cell Formulas
Range	Formula
B3:B9	B3	=VLOOKUP($A3,$E$3:$G$9,2,0)
C3:C9	C3	=VLOOKUP($A3,$E$3:$G$9,3,0)

 

[Peter_SSs]

Is this what you mean?

23 03 09.xlsm
	A	B	C	D	E	F	G
2	Account	Order No.	Reference No.		Account	Order No.	Reference No.
3	4300	1119	AB12		4300	1119	AB12
4	4300	1120	AB13		4300	1120	AB13
5	4301	1130	AB14		4301	1130	AB14
6	4302	1131	AB15		4302	1131	AB15
7	4302	1132	AB16		4302	1132	AB16
8	4302	1133	AB17		4302	1133	AB17
9	4302	1134	AB18		4302	1134	AB18
Lookup

Cell Formulas
Range	Formula
B3:B9	B3	=INDEX(F:F,AGGREGATE(15,6,ROW(E$3:E$9)/(E$3:E$9=A3),COUNTIF(E$3:E3,E3)))
C3:C9	C3	=INDEX(G:G,AGGREGATE(15,6,ROW(E$3:E$9)/(E$3:E$9=A3),COUNTIF(E$3:E3,E3)))

 

[Zubair]

Is this what you mean?

23 03 09.xlsm
	A	B	C	D	E	F	G
2	Account	Order No.	Reference No.		Account	Order No.	Reference No.
3	4300	1119	AB12		4300	1119	AB12
4	4300	1120	AB13		4300	1120	AB13
5	4301	1130	AB14		4301	1130	AB14
6	4302	1131	AB15		4302	1131	AB15
7	4302	1132	AB16		4302	1132	AB16
8	4302	1133	AB17		4302	1133	AB17
9	4302	1134	AB18		4302	1134	AB18
Lookup

Cell Formulas
Range	Formula
B3:B9	B3	=INDEX(F:F,AGGREGATE(15,6,ROW(E$3:E$9)/(E$3:E$9=A3),COUNTIF(E$3:E3,E3)))
C3:C9	C3	=INDEX(G:G,AGGREGATE(15,6,ROW(E$3:E$9)/(E$3:E$9=A3),COUNTIF(E$3:E3,E3)))

Yes thanks

 

[Fluff]

In your future questions, please mark the post that actually answered your question as the solution, not your post saying it worked. No further action is required for this thread.

 

[Peter_SSs]

Yes thanks

You're welcome. Glad it worked for you.