VLOOKUP is an essential Excel tool. Even if it’s the first function you learn, don’t worry, it’s actually pretty straight forward, and is really useful.
The purpose of the VLOOKUP function is to find your chosen value in a table of data and tell you what the corresponding value is in another part of that table. If that doesn’t make sense, follow this example and you’ll see what it means and why many Excel users consider it one of the most useful functions in their arsenal.
Currency conversion is essential when calculating international reward programs, like bonuses or salary review. You’ll usually need to work from a budget in a single currency to show the overall cost. So we’re going to use currency conversion to demonstrate the VLOOKUP function.
Here’s our spreadsheet:
It’ll be easier for you to follow this if you have your own spreadsheet open to work through it with me. I’ve copied and pasted the latest exchange rates for pounds sterling (my local currency here in England) from Reuters’ website in columns A and B (but you’ll probably want to be consistent with the rates your finance department is using).
In the BLUE cell, D1, you enter your currency, in the format shown in the table. You may want to use data validation to put a dropdown for this, but how to do that will be the subject of another post. For our example, enter “USD” in this cell (don’t enter the quotation marks).
In the ORANGE, D3, cell enter an amount, let’s say 100. This is the number of United States Dollars (USD) that you will convert into pounds sterling.
In the GREEN cell, D5, we’re going to do our VLOOKUP. You start by typing in “=vlookup(” like I have here:
The grey box will appear below showing how this function works. You’ll notice the first thing it says is lookup_value. In this case we’re going to look for “USD” and return the conversion rate next to it. We’ve already entered this in cell D1, so we’ll just point our VLOOKUP to that, like so:
The comma after D1 is to show Excel that we’ve moved on to the next part of the function, which they described as table_array in their helpful grey box. This is our table of currencies and conversion rates, held in cells A1:B5. Note that you always need to make the first column of this array the place where your lookup values are. In this case, we’re looking for “USD”, and we start the range in column A. So we add this to our formula thus:
Again, a comma moves us on to the next part, col_index_number. We need to tell Excel how many columns across our table we’re going to look. There are two columns in our table, and we’re looking for the value (the currency conversion rate) in the second column, so we enter the number 2:
Again, remember the comma. This is the last part now. VLOOKUP gives you the option of whether you want to find an exact match for your lookup_value (in our case “USD”) or not. You will almost exclusively want to find an exact match (approximate matches might not always be what you would expect), so you need to enter FALSE here. Try not to worry about this part too much. For the time being always FALSE. As you develop your skills you may find a problem that requires TRUE, but this doesn’t happen so much in common uses an HR practitioner might have. So we end up with this:
Notice that I’ve closed the brackets because this is the last part of the formula. I’ve also pressed enter, and you should too. So, what result does this formula give you? If you’ve followed everything so far, the answer should be 1.5875, the exchange rate for USD shown in our table. All you need to do now to make our example useful is multiply this by the value we entered in the orange cell, D3.
So select the green cell again, D5, and click at the end of your formula in the formula bar at the top of the screen. All we’re going to do is add “D3/” to the beginning of our formula, immediately after the =, meaning that we divide D3 by the result of our VLOOKUP. So your formula will now look like this:
The answer should now say 62.99 (and probably a fair few more decimal places), which is how many pounds you would get for $100. You can format this number to get rid of the extraneous decimal places – the shortcut for the formatting dialogue box is CTRL+1.
There is a copy of the finished spreadsheet here.