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.

Generic formula

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

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

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

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 column of the table...
Excel INDEX function

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 numbers....
Excel MATCH function

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 with the...

Related videos

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...

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 use the MATCH Function for exact matches

The MATCH function finds the relative position of an item in a list. MATCH can find exact matches or approximate matches. In this video, we'll look at how to use MATCH to find an exact match. The MATCH function takes three arguments: the lookup_value, which is the value you're looking up, the...

How to look things up with INDEX

What does the INDEX function do? Unlike the MATCH function , which gets the position of an item in a list or a table, INDEX assumes you already know the position, and it gets the value of the item at that position. Let's take a look. INDEX is a powerful and flexible function that can be used for...
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