Multiple Vlookup

[mira2020]

hi all,

how can i wrote a multiple Vlookup to fill in the value on the table in the right from data from the table in the left.
With the value in Item and From column can be changed

	From USA	From France
	Qty	Price	Qty	Price				Item	Qty	Price	From
Apple	100​	2​	200​	4​				Apple			USA
Orange	100​	4​	300​	5​				Orange

thank you

 

[awoohaw]

i assume France is supposed to be the last two columns of the first part.

 

[awoohaw]

Does this work for you:
It is hard coding the USA FRANCE lookup values.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1		From USA		From France
2		Qty	Price	Qty	Price				Item	Qty	Price	From
3	Apple	100	2	200	4				Apple	200	4	France
4	Orange	100	4	300	5				Orange	100	5	USA
5
Mira2020

Cell Formulas
Range	Formula
J3:K4	J3	=INDEX(($B$3:$C$4,$D$3:$E$4),MATCH($I3,$A$3:$A$4,0),MATCH(J$2,$B$2:$C$2,0),IF(L3="USA",1,2))

 

[Peter_SSs]

Does this work for you:

Shouldn't the Price for Orange from USA be 4?

how can i wrote a multiple Vlookup

Possibly this?

23 07 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		From USA		From France
2		Qty	Price	Qty	Price				Item	Qty	Price	From
3	Apple	100	2	200	4				Apple	200	4	France
4	Orange	100	4	300	5				Orange	100	4	USA
5									Apple	100	2	USA
6									Orange	300	5	France
Lookup

Cell Formulas
Range	Formula
J3:K6	J3	=VLOOKUP($I3,$A$3:$E$4,1+COLUMNS($J:J)+2*($L3="France"),0)

 

[Alex Blakenburg]

Or without hardcoding the country.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		From USA		From France
2		Qty	Price	Qty	Price				Item	Qty	Price	From
3	Apple	100	2	200	4				Apple	200	4	France
4	Orange	100	4	300	5				Orange	100	4	USA
5									Apple	100	2	USA
6									Orange	300	5	France
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=VLOOKUP($I3, $A$3:$E$4, MATCH("From " & L3, $A$1:$E$1, 0), FALSE)
K3:K6	K3	=VLOOKUP($I3, $A$3:$E$4, MATCH("From " & L3, $A$1:$E$1, 0)+1, FALSE)

 

[awoohaw]

Shouldn't the Price for Orange from USA be 4?


Possibly this?

23 07 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		From USA		From France
2		Qty	Price	Qty	Price				Item	Qty	Price	From
3	Apple	100	2	200	4				Apple	200	4	France
4	Orange	100	4	300	5				Orange	100	4	USA
5									Apple	100	2	USA
6									Orange	300	5	France
Lookup

Cell Formulas
Range	Formula
J3:K6	J3	=VLOOKUP($I3,$A$3:$E$4,1+COLUMNS($J:J)+2*($L3="France"),0)

I don't know what i did there. Obviously I didnt check my results. My bad.