How to Do Currency Conversion using VLOOKUP in Excel

This guide will explain how we can perform currency conversion using the VLOOKUP function in Excel.

The VLOOKUP function allows users to use lookup tables to return specific conversion rates.

For example, we can set up a table that includes two columns: the currency symbol of the target currency and the conversion rate from the base currency to the target currency. Afterward, we can use the VLOOKUP function to retrieve the needed conversion rate.

Let’s take a look at a quick example where we can use VLOOKUP and a lookup table.

Suppose you have a list of payments in US Dollars that you would like to convert into different currencies like Euros, Yen, or British Pounds. We can simply create a lookup table that includes the conversion rate between USD and EUR and so on.

Once the lookup table is set up, we can use the VLOOKUP function to find the proper conversion rate based on the indicated ISO currency code. ISO currency codes are an international standard that are used in banking and business. For example, the ISO code for the Euro is EUR and USD for the US Dollar.

Now that we know when to use the VLOOKUP function for currency conversion, let’s look at how it can be used on an actual sample spreadsheet.

 

 

A Real Example of Using VLOOKUP for Currency Conversion

Let’s take a look at a real example of the VLOOKUP function being used in an Excel spreadsheet for currency conversion.

In the example below, we have two tables that facilitate currency conversion. The first table includes our initial list of dollar amounts. Next to the amounts are the currencies that the user wants to convert to. The values in column C are the final amounts after the conversion. 

example of currency conversion in Excel using VLOOKUP

 

The second table is a USD conversion table for a select list of frequently traded currencies. The values in column F hold the ISO currency codes of the various currencies. In contrast, the values in column G are the actual conversion rates from the US Dollar to the indicated currency.

For example, the currency table indicates that 1 US Dollar is equal to 127.566 Japanese Yen. If we were to have 2 US Dollars, our equivalent in Yen would be 255.132. 

To get the values in Column C, we just need to use the following formula:

=VLOOKUP(B2;$F$3:$G$11;2;FALSE) * A2

The second argument in the VLOOKUP function is written as an absolute reference so that the reference does not change when we drag the formula down later on.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out the VLOOKUP function in Excel for currency conversion, let’s start writing it ourselves!

 

 

How to Do Currency Conversion using VLOOKUP in Excel

This section will guide you through each step needed to start using the VLOOKUP function in Excel to convert currencies. You’ll learn how we can use the lookup table to store a list of conversion rates that we can multiply by our base currency values.

Follow these steps to start using the VLOOKUP function for currency conversion:

  1. First, set up the lookup table with the proper conversion rates. Each row should include the proper currency symbol of each currency. You can read our guide on retrieving live currency data in Excel to set up this table quickly.
    lookup table
  2. Next, select the cell that will include the conversion formula. In this guide, we’ll start with selecting cell C2.
    select first cell
  3. Our currency conversion requires us to multiply the amount in the base currency by the target currency’s conversion rate. We’ll use the VLOOKUP function to retrieve the right conversion rate. We’ll use cell B2 as our lookup value and the cell range F2:G10 as the lookup range.
    use lookup table for currency conversion in Excel using VLOOKUP
  4. Next, we’ll multiply the result of our VLOOKUP value with the USD amount. In our table, we’ll multiply the VLOOKUP value with cell B2.
    multiply exchange rate with original amount
  5. Hit the Enter key to return the new converted amount.
    return converted currency amount
  6. Drag down the formula in the first cell to fill in the rest of the formula.
    example of sheets using currency conversion in Excel using VLOOKUP

 

 

Frequently Asked Questions (FAQ)

  1. Are there other functions you can use besides VLOOKUP for currency conversion with a lookup table?
    Users also can use the INDEX and MATCH functions to retrieve values from a lookup table. This particular method is useful when you need to look up values to the left of the given dataset or require a more dynamic formula.

 

 

That’s all you need to remember to start using the VLOOKUP function in Excel for converting values from one currency to another. This step-by-step guide shows how easy it is to use a lookup table to store conversion rates for a currency conversion formula.

The VLOOKUP function is just one example of a versatile function that you can use in your Excel spreadsheets. With so many other Excel functions, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like