A Beginner’s Guide to the Excel VLOOKUP Function
Level: Beginner – Intermediate
Even if you are relatively new to Excel, the chances are that you have at least heard about or encountered the
VLOOKUP Function in the spreadsheets of others, at some stage. So, you may be wondering what this function is all about and more importantly when and where to use it.
There is no denying that the
VLOOKUP Function is an extremely popular function, basically it’s a lookup function that is part of the lookup and reference functions, incorporated in Excel. The
VLOOKUP Function is used to retrieve your actual desired data, based on an input value that you enter.
The syntax and arguments for the
= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value is the value that you want to lookup and find a match for, in the table or range. This is the known value that you input or specify. This argument is required.
table_array is the table or range containing all the data that the function needs to search through. It’s a reference table basically or the lookup table containing both the lookup values and their matching corresponding return values. This argument is required.
col_index_num is the number of the column, of the table that you’d like to retrieve the matching data from. This argument is required.
[range_lookup] is an optional parameter and tells the function whether to return an approximate match in the case where the user species TRUE or an exact match in the case where the user specifies
FALSE. You can also enter the number 1 for
TRUE or 0 for
FALSE. If this argument is not specified, then the default value of
TRUE is used.
The syntax and arguments of any function are always easier to understand when demonstrated by using an example, so let’s look at two simple examples of how to use the
Exact Match Mode Example
A hypothetical baseball scout visited a few different states, created a list of players, and recorded his evaluation of their skill level in an Excel workbook. He recorded their
Player ID (the unique identifier that identifies each player), their names, the state they are from, their ages and their overall rating.
The baseball scout calculated the overall rating by looking at a combination of factors namely the batting average, hitting power and speed of the player at hand and based on the above factors, gave the player a rating of good, fair or poor.
The source data is shown below:
1) We’d like to enter/specify the
Player ID in cell I3 and then have the
VLOOKUP Function return the name of the player in cell J3. So, we enter the
Player ID in cell I3 as shown below.
2) We would like to retrieve the matching name of the player in cell J3, based on the
Player ID given in cell I3, so we enter the following formula in cell J3:
3) Upon pressing
VLOOKUP Function returns the value of Bradley Messer in cell J3 as shown below.
So let’s review this process in a little bit more detail:
lookup_value, we needed for the
VLOOKUPFunction, is the value specified in I3 – this is the specific
table_array, we needed for the
VLOOKUPFunction, this is the table/range where the function needs to search – is A4:E19.
col_index_num, we needed for the
VLOOKUPFunction, is the number of the column in the table/range that contains the exact data we’d like the function to retrieve. The way column numbering works is shown below:
- Since we wanted to retrieve the
Player Name, we entered 2, for the
FALSE, which means we want an exact match retrieved since we want the exact name that is associated with the specific
Player ID, entered as the
4) Let’s practice a little bit more, just to make sure we’ve got the hang of the
VLOOKUP Function. Now we’d like to enter the
Player ID in cell I7 and this time have the corresponding rating shown in cell J7. So, we enter the
Player ID in cell I7 as shown below.
5) In this case, we’d like to retrieve the
rating of the player in cell J7, based on the
Player ID given in cell I7, so we enter the following formula in cell J7:
6) Upon pressing
VLOOKUP Function returns the value of Poor in cell J7 as shown below.
So, that’s really all there is to it.
col_index_num (column number) would you use in the above example, if you wanted to enter the
Player ID in cell I7 and retrieve the corresponding state in cell J7, using the
Approximate Match Mode Example
A hypothetical manager of a sales team selling medical devices, would like to see the commission, different members of his sales team are entitled to, based on the number of devices sold. The reference table this manager is using, lists medical devices sold and the appropriate corresponding commission. The source data is shown below:
1) We’d like to use the
VLOOKUP Function in cell F4, in order to tell us how much commission the salesperson at hand (Alexa Rollins) is entitled to, based on the number of medical devices she sold (the
lookup_value in E4).
We can also see that in the above case, Alexa didn’t sell 10 devices or 20 devices exactly, so for this particular scenario, we can still use
VLOOKUP but specify an approximate match or
2) In order to retrieve the commission for salesperson Alexa Rollins, based on the 15 devices that she sold, we enter the following formula in cell F4:
3) Upon pressing
VLOOKUP Function returns the value of 2% commission in cell F4 as shown below.
So let’s review this process in a little bit more detail:
lookup_valuewe needed is the value specified in E4, this is the number of devices the salesperson at hand sold.
table_arraywe needed – this is the table/range where the function needs to search is A4:B10.
col_index_numwe needed, is the column in the table/range that contains the matching data we’d like the function to retrieve. In this case, we need the commission, so we enter 2.
range_lookupis TRUE, which means we want an approximate match retrieved since our salesperson did not sell 10 devices exactly or 20, instead our salesperson at hand sold 15, so in this case we will always use
- You will also see that the data in the reference table (in the leftmost column) was sorted in ascending order, this is necessary when using approximate match.
What would the commission of the salesperson at hand be, in the above example, if instead of 15 devices, the salesperson had sold 52 devices?
Useful Pointers for Using the VLOOKUP Function
The Most Common Error Message Associated with the VLOOKUP Function
VLOOKUP Function will return an
#N/A error if it can’t find the
lookup_value in the reference table, let’s say you specify a
lookup_value that your reference table does not contain or you spell the
Limitations of the VLOOKUP Function
VLOOKUP Function is extremely useful and easy to understand once you’ve practiced enough that is, you may find that occasionally you will need to use the more advanced
INDEX MATCH combination in order to lookup data. There are three main reasons for this:
INDEX MATCHcombo is faster than
VLOOKUP, which makes a difference if your worksheet contains a lot of data with many rows.
- With the
INDEX MATCHcombo, you can lookup data to the left as well.
VLOOKUPFunction can only handle data that is vertically arranged, whereas the
INDEX MATCHcombo can handle both horizontally arranged and vertically arranged data.
VLOOKUP Function is a very common, widely utilized lookup function within the Excel environment. So, it’s worthwhile taking the time to learn this function. On the plus side, it’s relatively simple, efficient and easy to understand. In addition, you can use it to lookup both exact and approximate matches. However, once you have more complicated lookup needs, it’s advisable to use the
INDEX MATCH combo, to handle the situations where
VLOOKUP falls short.
Learn more about the
VLOOKUP Function: The VLOOKUP Function Reference on the Office Website