Exceljet

Quick, clean, and to the point

Simple currency conversion

Excel formula: Simple currency conversion
Generic formula 
=VLOOKUP(currency,xtable,column,0)*amount
Explanation 

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
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.