Currency Conversion - IF argument?

Hi,
I need one cell to work out the value in sterling based on the currency a deal was done in.
I've imported exchange rates from X-rates.com, no problem there, but need
e.g. (sorry, can't seem to upload excel sheet for some reason.)

G4: Amount due exc. VAT = 1000
H4: Net Commission = 20% of G4
I4: Currency = £/$/EUR
J4: Net Commision in Sterling = THIS IS WHERE I NEED A FORMULA

I've got as far as "=IF(I4="$", (H4*[Cell ref for US Currency rate, OP knows where that is!])" but then I'm stuck with what to do. Formula needs to say if I4 is $, US rate, if it's EUR, EUR rate etc.

If the argument is FALSE i.e. if the deal was in £, then leave Net Commission...

Any help MASSIVELY appreciated, thanks in advance.

You could do something like this:

=IF(I4="$",US_rate_cell,IF(I4="EUR",EUR_rate_cell,1)) * H4

where you will need to put in the proper cell references for US_rate_cell and EUR_rate_cell.

Hope this helps.

Pete

Try


=$H4*VLOOKUP($I4,$L$1:$M$3,2,0)

I4 = currency e.g EUR , USD, GB etc


L1:M3 contains lookup table

EUR	1.3
USD	0.9
GB	1.0

If Cell I2 has US rate and Cell J2 has EUR rate then use this formula on J4:

=IF($I$4="$",$H$4*$I$2,IF($I$4="EUR",$H$4*$J$2,""))