Exceljet

Quick, clean, and to the point

Simple currency conversion

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

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.

Explanation 

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
Related formulas 
Excel formula: Map text to numbers
Map text to numbers
This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match. Although in this case we are mapping text values to...
Excel formula: Lookup up cost for product or service
Lookup up cost for product or service
This is a basic example of VLOOKUP in "exact match" mode. The lookup value is B6, the table is the range B5:C7, the column is 2, and the last argument, FALSE, forces VLOOKUP to do an exact match. (Read why this is necessary here ). If VLOOKUP finds...
Excel formula: Basic INDEX MATCH exact
Basic INDEX MATCH exact
This formula uses the MATCH function to get the row position of Toy Story in the table, and the INDEX function to retrieve the value at that row in column 2. MATCH is configured to look for the value in H4 in column B: MATCH ( H4 , B5:B9 , FALSE )...
Excel formula: Nested IF function example
Nested IF function example
This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests. Testing more than one condition If you need to test for more...
Related functions 
Excel VLOOKUP function
Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Excel INDEX function
Excel INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column...

Excel MATCH function
Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...

Related videos 
How to make a nested IF formula easier to read
By their nature, nested IF formulas are hard to read. In this video we show you how to easily make a nested IF formula more readable by adding line breaks.
How to replace nested IFs with VLOOKUP
In this video we look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements VLOOKUP is simpler, more transparent, and easier to adjust later.
How to use VLOOKUP
In this video learn how to use VLOOKUP to build a simple form to extract employee information from a table in about three minutes. VLOOKUP is a key Excel function.
How to use the MATCH Function for exact matches
In this video we look at how the MATCH function can find the position of an item in a list. This is a key idea in INDEX and MATCH formulas.
How to look things up with INDEX
In this video we look at how to configure INDEX to retrieve a value by row and column. This is the first half of the INDEX/MATCH equation.
See also 
19 tips for nested IF formulas

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.

500 Formulas | 101 Functions

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.