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.
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:
- 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.
- Next, select the cell that will include the conversion formula. In this guide, we’ll start with selecting cell C2.
- Our currency conversion requires us to multiply the amount in the base currency by the target currency’s conversion rate. We’ll use the
VLOOKUPfunction 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.
- Next, we’ll multiply the result of our
VLOOKUPvalue with the USD amount. In our table, we’ll multiply the
VLOOKUPvalue with cell B2.
- Hit the Enter key to return the new converted amount.
- Drag down the formula in the first cell to fill in the rest of the formula.
Frequently Asked Questions (FAQ)
- Are there other functions you can use besides VLOOKUP for currency conversion with a lookup table?
Users also can use the
MATCHfunctions 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.
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.