vlookup with multiple criteria

[Lizzi]

Hi All,

I need some assistance please. i'm using excel 2016.
Col W contains a revenue amount
Col U contains the currency used
Col X i need to contain the result of converting the rev amount in col w & col U

so for instance if col W is 50,000 col U is GBP col X should be 46,000 ( 50,000 * 0.92) i have a table that shows GBP, USD and JPY (col A) with the corresponding rate (Col B)

but how do i use a vlookup with 3 criteria?

Any help greatly appreciated as always

Thanku
Lizzi

 

[CDienst]

Why do you need 3 criteria? Wouldn't you need something like =VLOOKUP(U#,$A$1:$B$3,2,FALSE)? You probably would need to insert a column left of A to put the lookup in.

 

[Lizzi]

Why do you need 3 criteria? Wouldn't you need something like =VLOOKUP(U#,$A$1:$B$3,2,FALSE)? You probably would need to insert a column left of A to put the lookup in.

because im asking it to look at the currency in column U and if its GBP than * col W by 0.92 (in a table) if its USD then * it by xxx and if is JPY then * it by xxxx

so maybe its not a vlookup at all, maybe its something else

i want to say "if col u is GBP than * col W by 0.92 or if its JPY then * col W by xx or if it's USD then * col w by xxx"

 

[jtakw]

Hi,

Based on your description:

Book1
	A	B	C	T	U	V	W	X
1	GBP	0.92			GBP		50000	46000
2	USD	0.9			JPY		85000	74800
3	JPY	0.88			USD		74850	67365
Sheet238

Cell Formulas
Range	Formula
X1	=W1*VLOOKUP(U1,A$1:B$3,2,0)

Formula copied down.

 

[Lizzi]

Thanku that worked brilliantly........and because it worked so well, i realized i missed off something, col U contains 4 currencies, one being "Euro's" (which is not in my currency table) so if column U says euro, i need it to bring over the value in col W and not have to do the sum like the vlookup does

does that make sense?

so "if col u is GBP than * col W by 0.92 or if its JPY then * col W by xx or if it's USD then * col w by xxx if EURO's then col w"

 

[CDienst]

Wouldn't the easiest option be to add euros and their rate to the table? If euros are what you are converting to then just give it a table value of 1

 

[Lizzi]

Wouldn't the easiest option be to add euros and their rate to the table? If euros are what you are converting to then just give it a table value of 1

Oh yes, i didnt even think of that! thanku so much for your help

 

[jtakw]

Thanku that worked brilliantly........

You're welcome.