How to use the Excel VLOOKUP Function (Exact Mode/Approximate Mode)

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 Function:

= 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 VLOOKUP Function.

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:

=VLOOKUP(I3,A4:E19,2,FALSE)

3) Upon pressing CTRL-ENTER, the 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:

• Our lookup_value, we needed for the VLOOKUP Function, is the value specified in I3 – this is the specific Player ID.
• Our table_array, we needed for the VLOOKUP Function, this is the table/range where the function needs to search – is A4:E19.
• Our col_index_num, we needed for the VLOOKUP Function, 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 col_index_num.
• Our range_lookup is 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 lookup_value.

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:

=VLOOKUP(I7,A4:E19,5,FALSE)

6) Upon pressing CTRL-ENTER, the VLOOKUP Function returns the value of Poor in cell J7 as shown below.

So, that’s really all there is to it.

Which 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 VLOOKUP Function?

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 TRUE value.

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:

=VLOOKUP(E4,A4:B10,2,TRUE)

3) Upon pressing CTRL-ENTER, the 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:

• Our lookup_value we needed is the value specified in E4, this is the number of devices the salesperson at hand sold.
• Our table_array we needed – this is the table/range where the function needs to search is A4:B10.
• Our col_index_num we 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.
• Our range_lookup is 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 TRUE.
• 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?

The Most Common Error Message Associated with the VLOOKUP Function

The 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 lookup_value incorrectly.

Limitations of the VLOOKUP Function

While the 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:

• The INDEX MATCH combo is faster than VLOOKUP, which makes a difference if your worksheet contains a lot of data with many rows.
• With the INDEX MATCH combo, you can lookup data to the left as well.
• The VLOOKUP Function can only handle data that is vertically arranged, whereas the INDEX MATCH combo can handle both horizontally arranged and vertically arranged data.

Conclusion

The 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.