This VLOOKUP tutorial explains in simple terms how the function works, what its strengths are and also what limitations there are in using it to extract data from a range. The “V” in vlookup actually stands for “Vertical” indicating that Excel will search vertically – i.e. down a column – to find a match.
There is also a far less common HLOOKUP function in Excel that searches horizontally, i.e. across a row for a match, but we will not discuss that here.
What is a VLOOKUP function
The VLOOKUP function is by far the most common lookup in everyday use in Excel. It is used to pull in data from a range of cells and it works by referencing a single cell then matching it in the range you specify. When a match is found the function will return a value from a cell in a corresponding column.
VLOOKUP for Beginners
As with all Excel functions, this function can be entered directly into the cell or the formula bar, or it can be built using the Insert Function wizard (see image above). People who are new to Excel often find this a more convenient way of using functions and it can be handy to discover new functions you may not have used before.
The formula looks like this:
To make it a bit easier to understand, this is what it is really saying:
=VLOOKUP(Match,Table array,Column number)
In plain English, this means:
Match = What do you want me to find?
Table Array = Where should I look for it?
Column Number = If I find it, how many columns from the left is the data you want?
Will the function find an exact match?
There is a fourth option – Range Lookup – that is not required for the function to work, but can change the result of the lookup if it is used. This option allows you to tell Excel if the value you want returned is an exact match for the data you have told it to search for or not. It is the fourth element in the tooltip that appears under the vlookup formula and because it is optional, it is enclosed within square brackets:
If you choose to use this extra option you will be offered the choice of TRUE, which bizarrely returns an approximate match (and incidentally is the default action) or FALSE, which returns an exact match. You can also use the figure 1 for an approximate match and 0 for an exact match if you prefer.
VLOOKUP Tutorial 2010 Step by Step
The sample file uses an Excel 2010 VLOOKUP function but the same technique will work for all versions of Excel. In the sample file we are trying to find the appropriate rate of airport tax for each flight. The rates of tax are contained in a little table – the one with the blue background, and this will form our “table array”. What the VLOOKUP function will do is look for the name of the airport in this table and when it finds it, return the rate of tax from the column next to it. Simple, right?
Step 1. In the cell for Tax in the first record – Cape Town in this case – we start by typing an equal sign = and then VL to get Excel to find the VLOOKUP function:
Step 2. Press Tab or double-click to enter the function and the opening bracket:
Step 3. Now we must tell Excel what we want to match, in this case, the name of the airport. This is located in cell B6, so click in that cell like this:
Step 4. Excel puts the cell reference in and colours it blue, so now we must put in a comma:
Step 5. Now we move on to the next bit – the table array. We have to tell Excel where to look for a match, and we will want to copy the formula down the whole column when we have finished. This means we must “lock” the cell references to the table array. It is easy to do, but first we must select the cells, then press F4 on the keyboard to insert dollar signs in front of the column and row references:
Step 6. This will lock the cells that all the other rows will refer to. Now enter another comma:
Step 7. Next we have to tell Excel where the tax rate is in the table. The first column is always regarded as 1, so we want the rate of tax from the next column which will be 2. Type in 2, then another comma:
Step 8. We want an exact match, so now we must double-click FALSE, close the bracket and press enter:
Step 9. I want the cell formatted as a percentage, so I will click the % button on the ribbon:
Step 10. And we’re done! Now we can copy the formula down the entire column by double-clicking the fill handle, and Excel will find the correct rate of tax for each airport.
Notice that since we have told the Vlookup function to only return an exact match, “Manchester” returns an error message as it does not exist in our table array. In the real world we could avoid this by adding Manchester to the table with a rate of tax or by wrapping the VLOOKUP function inside an IFERROR function.
This is really useful if you have data that is likely to change, so you can use this technique for discounts, rates of surcharge, tax, commissions – anything in fact.
Limitations of the VLOOKUP function
There are limitations of a VLOOKUP function, in that it will only search the leftmost column of any range of cells and it will only return a value to the right. Another issue can be that you have to know in advance how many columns across the data you want is located.
For more flexibility, try using INDEX and MATCH functions. These combined functions will return a value from anywhere in the workbook or even another workbook entirely. When used in conjunction with Tables in Excel they can use the power of structured references to simplify the process even more.