## A Beginner’s Guide to the Excel VLOOKUP Function

**Level**: Beginner – Intermediate

Download the example files to follow along

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?**

### Useful Pointers for Using the VLOOKUP Function

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

Learn more about the `VLOOKUP`

Function: The VLOOKUP Function Reference on the Office Website

Move onto Part II: Double VLOOKUP (Nested VLOOKUPs) >>>