Summary

To convert from a given currency to other specific currencies, you can use the VLOOKUP function with a lookup table that contains conversion rates. In the example shown, the formula in E5 is:

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

Where xtable is the named range G5:H10. As the formula is copied down, it converts the amount in column B from US Dollars (USD) to the currency indicated in column D.

Generic formula

=VLOOKUP(currency,xtable,column,0)*amount

Explanation 

The formula in this example converts amounts in USD to other currencies using currency codes and a simple lookup table. The 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)

The inputs to VLOOKUP are given as follows:

  • lookup_value - the currency code in D5
  • table_array - the currency conversion table in G5:H10, given as the named range xtable
  • col_index_num - 2, because we want to return values from column H
  • range_lookup - 0,m because we want VLOOKUP to perform an exact match

For more information on VLOOKUP, see this detailed overview.

In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H. The result from VLOOKUP is then multiplied by the value in B5, which is the number of US dollars to convert.

Note: If a currency code does not exist in the table, VLOOKUP will return a #N/A error.

Nested IF equivalent

Note that it is possible to build a formula to do a currency conversion by nesting multiple IF functions together and hardcoding the currency rates directly into the formula 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

Note: in the formula above, line breaks have been added for better readability.

I present this option here to illustrate how the VLOOKUP option is superior. It's a much simpler formula, and the exchange rate values are not stored redundantly in many different formulas. In addition, keeping the lookup table on the worksheet makes it easy to see and edit the exchange rates (in one place) at any time.

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 numeric outputs, the same formula can handle text 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 a matching value, the associated cost will be...
Excel formula: INDEX and MATCH exact match

INDEX and MATCH exact match

In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...
Excel formula: Nested IF function example

Nested IF function example

The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for each...

Related functions

Excel VLOOKUP function

VLOOKUP Function

The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number. VLOOKUP supports approximate and exact matching...
Excel IF function

IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...

Related videos

How to use VLOOKUP

VLOOKUP is one of the most important lookup functions in Excel. The V stands for "vertical" which means you can use VLOOKUP to look up values in a table that's arranged vertically. Let's take a look. Here we have a list of employees in a table. Let's use VLOOKUP to build a simple form that...

How to make a nested IF formula easier to read

In this video we're going to look at how to make a nested IF formula more readable by adding line breaks. Here I have a worksheet that calculates sales commissions based on the commission structure shown in the table. For example, we can see that King sold $124,500 and gets a commission of 5%,...

How to replace nested IFs with VLOOKUP

You might build or inherit a worksheet that uses a series of nested IF statements to assign values of some kind. Many people use nested IF statements this way because the approach is easy once you get the hang of it. But nested IF statements can be difficult to maintain and debug. Let's look at how...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Videos

Articles

Training