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

Related formulas

Map text to numbers

To map or translate text inputs to arbitrary numeric values, you can use the VLOOKUP function with a simple table. In the example, we need to map five text values (statuses) to numeric status codes as follows: Status Code Started 10 Normal 20...

Lookup up cost for product or service

If you have a list of products or services (or related options), with associated costs, you can use VLOOKUP to find and retrieve the cost for a specific option. In the example shown, the formula in cell F6 is: = VLOOKUP ( E6 , B5:C7 , 2 , FALSE )...

Basic INDEX MATCH exact

This example shows how to use INDEX and MATCH to get information from a table based on an exact match. In the example shown, the formula in cell H6 is: = INDEX ( B5:E9 , MATCH ( H4 , B5:B9 , FALSE ), 2 ) which returns 1995, the year the movie Toy...

Nested IF function example

In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it's own set of parentheses. This article describes the Excel nested IF construction. Usually, nested...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...

Excel INDEX Function

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...

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, the INDEX...

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.