Not sure which formula to use to replace a vlookup

[Lizzi]

Hi All,

I'm using excel 2016 and have been using a vlookup to help with my data, i am now starting to see multiple lines that have the same lookup value but the value is different...so i know that vlookup will no longer work for me as it looks only at the first row to give me the results but i'm unsure as to what formula will.



Many thanks for any help given

Lizzi

 

[mackc557]

How would someone know the difference between Row 4 and Row 5 when looking at the PO Number? I don't entirely know what you are working with, but if you need the same PO multiple times and kept in a specific order (chronological or otherwise), consider using "POXXX12.1", "POXXX12.2", etc.

 

[Sufiyan97]

Is that what you want?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	PO Number	Currency	Local currency	Euro	Comments		PO Number	Currency	Local currency	Euro	Comments
3	POXXXX12	USD	2000	1709	Not needed		POXXXX12	USD	2000	1709	Not needed
4	POXXXX12	USD	5000	1709	Not needed		POXXXX12	USD	5000	4274
5	POXXXX12	USD	2750	1709	Not needed		POXXXX12	USD	2750	2350
6	POXXXX12	USD	0	1709	Not needed		POXXXX12	USD			Included on above line
7	POXXXX12	USD	1500	1709	Not needed		POXXXX12	USD	1500	1282
8	POXXXX13	USD	957	1709	Not needed		POXXXX13	USD	957	818
9	POXXXX14	USD	542	818	0		POXXXX14	USD	542	463
10	POXXXX26	USD	125	463	0		POXXXX26	USD	125	107
11	POXXXX52	USD	0	107	0		POXXXX52
12
13
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=INDEX($I$3:$I$11,AGGREGATE(15,6,(ROW($I$3:$I$11)-ROW($I$3)+1)/($G$3:$G$11=A3),COUNTIF($A$3:A3,A3)))

 

[Lizzi]

nearly...i think the "euro" column looks wrong thou?

really appreciate you helping me, thanku

 

[Lizzi]

How would someone know the difference between Row 4 and Row 5 when looking at the PO Number? I don't entirely know what you are working with, but if you need the same PO multiple times and kept in a specific order (chronological or otherwise), consider using "POXXX12.1", "POXXX12.2", etc.

they wouldn't but i would.....and i can't change the PO number, unfortunately it is what it is

 

[Sufiyan97]

nearly...i think the "euro" column looks wrong thou?

really appreciate you helping me, thanku

Updated Euro column

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	PO Number	Currency	Local currency	Euro	Comments		PO Number	Currency	Local currency	Euro	Comments
3	POXXXX12	USD	2000	1709	Not needed		POXXXX12	USD	2000	1709	Not needed
4	POXXXX12	USD	5000	4274	Not needed		POXXXX12	USD	5000	4274
5	POXXXX12	USD	2750	2350	Not needed		POXXXX12	USD	2750	2350
6	POXXXX12	USD	0	0	Not needed		POXXXX12	USD			Included on above line
7	POXXXX12	USD	1500	1282	Not needed		POXXXX12	USD	1500	1282
8	POXXXX13	USD	957	818	Not needed		POXXXX13	USD	957	818
9	POXXXX14	USD	542	463	0		POXXXX14	USD	542	463
10	POXXXX26	USD	125	107	0		POXXXX26	USD	125	107
11	POXXXX52	USD	0	0	0		POXXXX52
12
13
14
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=INDEX($I$3:$I$11,AGGREGATE(15,6,(ROW($I$3:$I$11)-ROW($I$3)+1)/($G$3:$G$11=A3),COUNTIF($A$3:A3,A3)))
D3:D11	D3	=INDEX($J$3:$J$11,AGGREGATE(15,6,(ROW($I$3:$I$11)-ROW($I$3)+1)/($G$3:$G$11=A3),COUNTIF($A$3:A3,A3)))

 

[Lizzi]

thanku so much, that worked