The Excel VLOOKUP function is one of the most popular functions in Excel and one I certainly use on a regular basis. In this tutorial I will explain what is the Excel VLOOKUP function and how to use it.
What is Excel VLOOKUP Function?
The Excel VLOOKUP function allows you to extract a value from a given column in a table by looking at the lookup value.
The lookup value must appear in the first column in the table. The values you want to extract must appear in the columns to the right.
VLOOKUP stands for Vertical Lookup.
The syntax for the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value - The value you want to look for in the first column of the lookup table
table_array - The table where you want to extract the value
col_index_num - The column number in the table where you want to extract the value
range_lookup - This is an optional argument where FALSE is to retrieve the exact match and TRUE an approximate match
Now let's have a look at an example of using the VLOOKUP function to extract an exact match and then an approximate match.
VLOOKUP to Extract an Exact Match
VLOOKUP to extract an exact match is the most common method of using the VLOOKUP function.
In this example there is a table which contains a list of sales people, the area which they cover and their sales in quarter 1.
I want to extract the area Peter covers and what sales he has made in quarter 1.
To extract the area Peter covers the VLOOKUP formula is =VLOOKUP(G3,$B$2:$D$9,2,FALSE)
The lookup_value argument is cell G3 as "Peter" is the value I want to lookup in the table.
The table_array argument is the range B2:D9, i.e. the range where the table is located.
The col_index_num argument is 2. I want to extract the area for Peter and this is located in the second column of the table.
The range_lookup is FALSE as I want to extract the exact match.
Note: For the range_lookup argument you can enter a 0 instead of FALSE. If you do not enter anything in the range_lookup argument then it will default to TRUE i.e. an approximate match.
Notice the lookup values, i.e. the sales people is in the left most column of the table. If it wasn't then the VLOOKUP formula will not work.
Now let's extract the sales for Peter.
To extract the sales for Peter, the only difference in the VLOOKUP formula is the col_index_num argument. This argument is now 3 as the sales figures are in the third column of the table.
The formula is now:
A good tip is to name your table. This is particularly true if you have a lot of VLOOKUP formulas in your spreadsheet. By doing this you will know which tables the VLOOKUP formulas are referring to if you have given them relevant names.
You can do this by highlighting the table and then naming it in the Name Box and then press Enter on your keyboard. If you are using more than one word to name the table then you can't have a space between the words. You should name it so that it is relevant to what the table is showing. In my example, I have called the table "Sales_Table". I could have also named it "SalesTable".
The VLOOKUP formula to extract the area for Peter would now be:
To extract the sales it would now be:
Now let's have a look at using the VLOOKUP function to extract an approximate match.
VLOOKUP to Extract an Approximate Match
Sometimes you have a set of data where you cannot extract an exact match from a lookup value. In this instance you will need to extract the best match for the lookup value.
For the approximate match, let's take the data I used for the exact match example a stage further.
I have the same list of sales people with their sales. I now want to allocate them a commission % in column E so I can work out how much commission I can pay them in column F. The table which allocates the commission % based on the sales achieved is in the range H2:I8. You can see that most of the sales figures in column D does not match the sales figures in the lookup column in column H so an approximate match needs to be made.
The formula in cell E3 is =VLOOKUP(D3,$H$3:$I$8,2,TRUE)
The lookup_value argument is cell D3 which is the sales figure. The sales is the value I want to lookup in the table in column H.
The table_array argument is the range H2:I8, i.e. the range where the table is located.
The col_index_num argument is 2. I want to extract the commission % and this is located in the second column of the table.
The range_lookup is TRUE as I want to extract the approximate match.
Note: For the range_lookup argument you can enter a 1 instead of TRUE or you can omit this argument altogether as the default setting for this is TRUE.
If the VLOOKUP function cannot find the exact sales value in column H, it will return the largest value smaller than the sales value. Let's now look at the results in column D to understand how the VLOOKUP approximate match works.
For cell E3 the commission rate is 12% as £900 sales earns a commission of 12% based on the table
For cell E4 the commission rate is 13% as £1,050 sales is above £1,000 but below £1,200 so it uses the lookup value of £1,000.
For cell E5 the commission rate is 14% as £1,250 sales is above £1,200 but below £1,500 so it uses the lookup value of £1,200.
For cell E6 the commission rate is 11% as £860 sales is above £600 but below £900 so it uses the lookup value of £600.
For cell E7 the commission rate is 14% as £1,450 sales is above £1,200 but below £1,500 so it uses the lookup value of £1,200.
For cell E8 the commission rate is 11% as £680 sales is above £600 but below £900 so it uses the lookup value of £600.
For cell E9 the commission rate is 11% as £758 sales is above £600 but below £900 so uses the lookup value of £600.
Note: the lookup table must be sorted in ascending order by lookup value otherwise you will not get the correct results.
Excel VLOOKUP Function Tutorial Video
Please see my video for further examples of what is the VLOOKUP function and how to use it to extract an exact and approximate match.
Limitations of the Excel VLOOKUP Function
There are a couple of major limitations of the Excel VLOOKUP function:
1. As I have already mentioned, the lookup value must be in the left most column of the lookup table. VLOOKUP only looks to the right.
2. VLOOKUP only returns the first match. If you have multiple values in the lookup table that match the lookup value, it will only return the first value.
Advanced VLOOKUP Add-in
I have created an Excel Add-in which will eliminate these issues called the Advanced VLOOKUP Add-in. Please click here to buy the Excel Add-in.
For a demonstration of how the Add-in works please see my video.
I hope you enjoyed my tutorial on what is the Excel VLOOKUP function. If you would like to give me your feedback on this tutorial or you have any questions then please leave me a comment below and I will get back to you.