Match a currency with its corresponding value

[ali]

i have 2 worksheets in one excel file

------------------------------------------------------------------------------------------
sheet1:

Currency ---------------Amount

AUD --------------- 25,000,000
JPY --------------- 85,000,000
EUR --------------- 1,000,000

------------------------------------------------------------------------------------------

Sheet 2: (Currency Cross-Rate in USD)



USD--------------- $1.00
GBP--------------- $2.049194991
EUR--------------- $1.379994037
JPY--------------- $ 0.008185748
AUD ------------- $ 0.880441264

--------------------------------------------------------------------------------------

Question:
I need to generate a function which does the following:

IF a currency is "EUR" (in sheet1) , the next cell should multiply itself
with 1.379994037 (in sheet2).

So i have a list of currency and amounts in different currencies. THe
functions should automatically match the Currency and multiply it's value.

IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied
with USD equavelent (which is $0.008185748).

All currencies should be in USD-equivelent.

DOn't worry, i have my own currency sheet !


Thank you so much !

---------

 

[Bob Phillips]

=B1*VLOOKUP($A1,Sheet2!$A:$B,2,FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Ali,

Put this formula next to your currency amounts on sheet 1 and drag down.

=IF(ISNA(B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE))),"Conversion factor not
found",B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE)))


If it finds the currency it will perform the multiplication, If not it
returns conversion factor not found.

Mike

 

[ali]

THank you so much bob, your formula works perfectly !! Now i can carry on my
work !!

Keep it up!!

 

[ali]

Mike,

Thank you so much as well. Both yours and Bob's work perfetly, and your
formula is very nice , it took me a while to interprete it!! You must be an
expert! ! ! Once again, thank you so much !

Keep it up!