How to Use the VLOOKUP Function

In this blog post, we’re going to take a look at how to use the VLOOKUP function. The VLOOKUP function is one of the most commonly used and asked about Excel functions.

The VLOOKUP function enables you to perform a very useful method of finding data in a worksheet known as a lookup. A lookup can be extremely useful when working with large amounts of data and you want to dynamically find a specific piece of information based on a value. The VLOOKUP function is probably the simplest of all methods of carrying out a lookup.

The VLOOKUP function finds the row where a specific value is in the leftmost column in a range of cells. The VLOOKUP function then returns a value from a cell on the same row but in a different column. You can specify the column from which to return the value.

If you want to download the example spreadsheet used in this blog post and a PDF guide to the VLOOKUP function, click the download button below.

Quick Example

The goal of the VLOOKUP function is to search a range of cells known as a table_array for a value known as the lookup_value. Once the VLOOKUP function finds the lookup_value, it returns a value in a corresponding number of columns to the right that is specified by the col_index_num parameter.

This principle is represented in the image below.

VLOOKUP Princple Explained

The V in the term VLOOKUP stands for “vertical.” The reason why vertical is important is that the VLOOKUP function searches for the lookup_value vertically in the leftmost column of a range of cells, known as the table_array, starting from the top and working downward.

Let’s take a quick look at an example. Imagine we have a worksheet containing employee data. In the first column is the employee’s name, and each column to the right of the employee’s name contains details specific to the employee. We’re going to use the VLOOKUP function to search for an employee called Delilah and get the VLOOKUP function to return her position title using the following formula.

=VLOOKUP (F3, A1:D21, 3, 0)

We simply insert this formula in cell G2.

VLOOKUP Quick Example

The formula returns the value Development Manager as Delilah’s position, which is correct.

Syntax

The syntax of the VLOOKUP function is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

ParameterDescription
lookup_valueThis is the value that the VLOOKUP function searches for in the leftmost column of the table.
table_arrayThis is the range of cells that the VLOOKUP function uses. The first column of the selected range of cells will be the one in which the lookup_value will be looked up.
col_index_numThis is the number of the column to the right of the leftmost column that you want to extract the data from. The numbering starts at 1, being the leftmost column.
range_lookupThis is an optional logical value. The default value is 1 (TRUE). This means that it will search for the closest numerical or alphabetical value to your lookup_value if it can`t find your exact search. The 0 value (FALSE) will specifically search for your lookup_value, and it will provide the #N/A value if it can`t find it.

Example

Returning to our earlier example where we searched for Delilah, we used the VLOOKUP function to find the position of a specific employee in a range of cells, using the employee’s name as the lookup_value.

The formula used in this example was:

=VLOOKUP (F3, A1:D21, 3, 0)

Let us break this formula down:

  • lookup_value – the lookup_value is F3, which is a reference to a cell where a user inputs a lookup_value. In our example, we are looking for Delilah. You can also replace the cell reference to F3 with the actual name of the employee. This would change the formula to look like =VLOOKUP (“Delilah”, A1:D21, 3, 0). The result would be the same.
  • table_array – the table_array parameter is A1:D21, and it represents the range of cells to be used by the VLOOKUP function. Our table_array is the range of cell A1 to D21.
  • col_index_num – the col_index_num parameter is 3. This specifies the number of columns to the right in the table_array that we will return our value from. Column C is the third column of our table_array.
  • range_lookup – the range_lookup parameter value is 0. This means that the VLOOKUP function will carry out an exact match for the lookup_value Delilah. If it doesn’t find her within the table_array, it will display the #N/A

Case-Sensitivity

The VLOOKUP function on its own isn’t case-sensitive. That means it won’t differentiate between DELILAH, Delilah and delilah. It will match them exactly the same.

If you have a worksheet that contains the same values but a different case in the lookup column, it will match the first value it finds. So for instance, if you have Delilah, Delilah and delilah in the leftmost column of your table_array, it will match the first value it finds as shown in the image below.

VLOOKUP Case Sensitivity

Range Lookup Values

The VLOOKUP function range_lookup parameter can take two different values. The value can be number 0 or the word FALSE. Alternatively, it can be 1 or the word TRUE. If no range_lookup parameter value is supplied, the VLOOKUP function defaults to the value 1 (TRUE).

If the value is set to 0 (FALSE), the VLOOKUP function will carry out an exact match against the lookup_value. However, if the range_lookup parameter value is 1 (TRUE), the VLOOKUP function will carry out an approximate match.

Exact Match

With the exception of case sensitivity, when carrying out an exact match, the VLOOKUP function will only return a value if it finds an exact match to the lookup_value in the leftmost column of the table_array.

In our previous example, we used the following formula:

=VLOOKUP(F2,A1:D21,3,0)

Notice that the last value, the lookup_value, is 0. Previously, we have searched for Delilah. However, we could have searched for Ken as shown in the image below.

VLOOKUP Exact Match

If an exact match isn’t found, then the VLOOKUP function will return the #N/A error as shown in the image below.

VLOOKUP Exact Match #NA Error

Approximate Match

VLOOKUP uses approximate match by default. When using an approximate match, it is best to have the leftmost column sorted either alphabetically or numerically. If the leftmost column isn’t sorted in this manner, the VLOOKUP function may return unexpected values.

For example, if we take our previous example and change the range_lookup value to 1, the formula now looks like this:

=VLOOKUP(F2,$A$1:$D$21,3,1)

If then sort our table in reverse alphabetical order (Z-A), using the following button on the Data tab of the Ribbon.

The Windows and Mac menus look slightly different.

Sort ZA Button

The formula now returns the #N/A error even though our lookup_value Ken exists in our table_array. As shown below.

VLOOKUP Approx Match #NA Error

The approximate match will return a value in the following scenario. First, let’s sort our data alphabetically (A-Z). Use the following button on the Data tab of the ribbon.

Sort AZ Button

Now type Kev, not Ken in cell F2. This name doesn’t exist in our table_array.

VLOOKUP Approx Match

This time, the formula returns the value Excel Master. This is because the name Kev is an approximate match to the name Ken, which is in our table_array.

Using approximate matching can be unpredictable. I strongly suggest using the exact match value 0 for the range_lookup parameter unless you have a very specific reason not to.

Preparing Your Data

If designing your spreadsheet from scratch, it is worth spending five minutes thinking about the data you intend to collect, use, and/or analyse. For the VLOOKUP function, there are a couple of golden rules to think about.

  1. The leftmost column must contain your lookup_value. Try to keep these values unique and have a value for every row.
  1. Keep all data that relates to the lookup_value on the same row. Use different columns for each piece of data that relates to the lookup_value. Make sure you use the same column on each row for the same types of data, i.e., a column for telephone numbers and another column for job titles.

Reference Types

If you intend to copy and paste a formula containing the VLOOKUP function, absolute references can save you a lot of time and editing. For instance, in our example, if we want to return more data relating to a specific employee, such as the employee’s salary, we could copy and paste the same formula into cell H2 and add a new heading, Salary, to cell H1 like in the image below:

VLOOKUP Function copy and pasted with relative references Example 6

The formula now looks like this:

=VLOOKUP(G2,B1:E21,3,0)

However, this formula now references the range of cells B1:E21 in the table_array parameter and the cell G2 for the lookup_value parameter. The formula returns a #N/A error because it is now looking for the lookup_value in column B, which doesn’t contain the employees’ names.

This is because we have pasted the formula one column to the right, and Excel updated the cell references so that they now reference cells one column to the right.

To get around this, we can use absolute cell references. To create absolute cell references, we place a dollar sign ($) in front of either or both the column and row reference.  Going back to our original formula, we can make our cell references absolute as follows:

=VLOOKUP($F$2, $A$1:$D$21,3,0)

We can now copy and paste this formula in to cell H2. When we finish, the worksheet will look like the image below.

image 11 vlookup copy and pasted formula absolute references col_index 3 example 7

It is now returning the Position value for Ken not his Salary in cell H2. We need to update the col_index_num parameter value to 4 so that it returns the salary information. Once we’ve done this, the formula should now look like this:

=VLOOKUP($F$2,$A$1:$D$21,4,0)

Our worksheet now returns the correct salary information.

Image 12 VLOOKUP Copy and Pasted Formula Absolute References Example 7 v2

For more information on Absolute references, check out my post by clicking here.

Named Ranges

Using named ranges with the VLOOKUP function, like most other functions, makes the VLOOKUP function easier to read and understand. Named ranges also make spreadsheet maintenance easier. Using a named range for your table_array parameters enables you to add rows to the table_array without needing to update your formula containing the VLOOKUP function.

To do this, we need to create named ranges for the employee data. Start by selecting the cells A1:D21 and type EmployeeData in the name box and press enter as shown in the image below.

Create Named Range Example

Now, we can use the named range EmployeeData in the formula as follows:

=VLOOKUP(F2,EmployeeData,3,0)

The formula is now much easier to read, plus you can change the size of the table_array without touching the formula. Once this is done, we can insert the formula into cell G2.

VLOOKUP Function Named Range Example 8

For more information on named ranges, check my post by clicking here.

Wildcard Characters

When carrying out an exact match, you can use wildcard characters in the lookup_value parameter. For instance, you may want to search a list of employee names that start with the text Ric. To do this, we would use the following formula:

=VLOOKUP(F2&”*”,$A$1:$D$21,3,0)

Note that we concatenate the value input into cell F2 with the wildcard character, asterisk (*), using the expression F2&”*”. This will match the first value found that starts with the value input into cell F2, irrespective of the text that follows. When we insert the formula into our worksheet, it returns the value Analyst. This is because the name Richard, from our table_array, matches our lookup_value Ric*.

VLOOKUP Function Wildcard Example 9

For more information on wildcard characters, check my post by clicking here.

Common Errors

The VLOOKUP function can return several different errors.

#N/A Error

When carrying out an exact match, (range_lookup value 0 or FALSE), the VLOOKUP function returns the #N/A error when the lookup_value can’t be found.

If carrying out an approximate match, (range_lookup value 1, or TRUE), the VLOOKUP function returns the #N/A error when the lookup_value is smaller than the smallest value in the left most column of the table_array. This might sound a little weird when the lookup_value is a text string, but all values in Excel have a numerical equivalent.

If we carry out an exact match using our example, and we input the value David into cell F2. It returns the #N/A error.

IMage 16 VLOOKUP NA Error

This is because our table_array doesn’t contain the name David in the first column.

You can trap #N/A errors and display a friendly message using the IFNA function.

We can modify our formula to include the IFNA function as follows:

=IFNA(VLOOKUP(F2,$A$1:$D$21,3,0),”No Match”)

When the VLOOKUP function returns the #N/A error, the IFNA function replaces the error message with the text “No Match,” as shown below.

Image 17 VLOOKUP IFNA Function Example 11

This text is a little more user-friendly. For more information on the IFNA function, click here.

#REF! Error

The #REF! Error is likely to occur if the col_index_num isn’t a valid value and is higher than the number of columns in the table_array.

For instance, if we modify our formula as follows:

=VLOOKUP(F2,$A$1:$D$21,5,0)

The formula is now referenced s col_index_num 5. Our table_array only has 4 columns, which results in the #REF! error.

Image 18 VLOOKUP #REF! Error

#NAME? Error

The final error we will discuss in relation to the VLOOKUP function is the #NAME?. The main reason you might get the #NAME? error is a typo in the formula. The formula thinks you have referenced a name such as a Named Range, Named Constant, or another function.

For instance, if we type the following formula into our worksheet:

=VLOOKUP(F,$A$1:$D$21,3,0)

This formula will return the #NAME? error.

Image 19 VLOOKUP #NAME? Error Example 13

This is because Excel thinks we have referenced a named name of some kind called F, when in fact, we wanted to reference cell F2 but forgot to type the number 2 after the letter F. Typed correctly, our formula looks like this:

=VLOOKUP(F2, $A$1:$D$21,3,0)

This formula now works just fine as shown on the next page.

Image 20 VLOOKUP #NAME? Error Correct Example 13

Limitations of the VLOOKUP Function.

One of the key problems with the VLOOKUP function is that you can only look up values to the right of the first column in the table_array. However, what If the employee’s names, the lookup_value, was in a column that is to the right of the values we wanted to return. For instance, in the image on the next page, we wouldn’t be able to return the employee’s age.

Image 21 VLOOKUP lookup values in column

This is where INDEX MATCH comes in. INDEX MATCH can achieve everything VLOOKUP can, but is a little more complicated to learn. Click here to read my post on how to use INDEX MATCH.

Wrap-up

That’s all for this blog post. I hope you’ve found it useful. As you can see, the VLOOKUP function can be useful. You’ve learned the difference between exact and approximate matching. You’ve seen how to use the VLOOKUP function with named rangesabsolute and relative references, and wildcard characters; and you have seen some of the errors that can occur.

Despite some of the VLOOKUP function shortcomings, it is still an extremely popular and useful function.

That’s it for this post.  If you’d like a copy of the spreadsheet used in this post, please sign up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit