Simple currency conversion

To convert from a given currency to other specific currencies, you can use the VLOOKUP function. In the example shown, the formula in E5 is:

=VLOOKUP(D5,xtable,2,0)*B5

which converts the amount in column B from US Dollars (USD) to the currency indicated in column D, where the named range "xtable" refers to G5:H10.

How this formula works

The formula in this example converts amounts in USD to other currencies using currency codes. Available currencies and exact conversion rates can be adjusted by editing the values in the table on the right.

The core of this formula is the VLOOKUP function, configured like this:

=VLOOKUP(D5,xtable,2,0)

Here, the lookup value comes from column D, table array is the named range "xtable", column index is 2, and match type is set to exact match.

In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H. If the currency code 

Nested IF equivalent

With a formula based on nested IFs, the solution looks like this:

=IF(D5="usd",1,
IF(D5="eur",0.84,
IF(D5="yen",112.35,
IF(D5="can",1.23,
IF(D5="gpb",0.74,
IF(D5="cny",6.59))))))*B5

Line breaks added for better readability.

Note: VLOOKUP is generally a better solution, since values are easy to see and edit at any time, the formula is easier to read, and exchange rate values are not stored redundantly in many different formulas.

INDEX and MATCH equivalent

With the INDEX and MATCH funcions, the solution looks like this:

=INDEX($H$5:$H$10,MATCH(D5,$G$5:$G$10,0))*B5