How to Use VLOOKUP in Excel?: A Step-by-Step Guide

VLOOKUP stands for Vertical Lookup. As the name specifies, VLOOKUP is a built-in Excel function that helps you look for a specified value by searching for it vertically across the sheet. VLOOKUP in Excel may sound complicated, but you will find out that it is a very easy and useful tool once you try it. Look at the example below to understand VLOOKUP.

The VLOOKUP formula below looks for a Company name with Company ID 3.

In the next section, you will understand how to use the VLOOKUP function.

VLOOKUP Formula

VLOOKUP(lookup_value, table_array, col_index_number,[range_lookup])

Where,

lookup_value: This specifies the value that you want to look up in our data.
table_array: This is the location where the values are present in excel.
col_index_number: This specifies the column number from where we need to return the value.
range_lookup: This has two options; if the value is set to FALSE, that means we are looking for an exact match. If the value is TRUE, then we are looking for an approximate match.

How To Find an Exact Match Using VLOOKUP?

VLOOKUP makes it effortless to look for an exact match from the table. Let’s take a look at how to do this with the help of an example:

In the example below, we are using the VLOOKUP function to find the value of the exact match of ID from the given table. So, we set the first parameter as the lookup value, which is the cell H5.

We specify the location of the table in the second argument. As you can see, the table location is A2:F11.
The third argument specifies the Column Index number. This tells us what value should be returned from the row that we are looking up for. In the example, the product column is 3.
The last argument is a Boolean Expression. Here, the value is set to FALSE for the VLOOKUP function to return an exact match for the value. An N/A error is displayed in case the exact value is not found.

With the help of VLOOKUP in Excel, we can look for an approximate match as well. You will learn this in the next section.

How To Find Approximate Match Using VLOOKUP?

Approximate Match works by finding the next largest value that is lesser than the lookup value, which we specify.

In the example below, we use the VLOOKUP function to find out how much RAM specification a laptop priced 1300 EUR has. Also, we know this value is not present in the table. So, let’s use the Approximate Match to find the solution. We need to sort the first column in ascending order. If not, VLOOKUP will return incorrect values.

First, copy the price and RAM details to a new location, and specify your lookup value. Here, the lookup value is $1300.

Next, select your data range and click on the filter option to sort the values of the first column based on ascending order. After you click on the filter option, you will see the filter buttons enabled on your column headers.

Filter the price details in ascending order. Click on OK.

Now, enter your VLOOKUP formula. The first argument will be the VLOOKUP value. The second argument specifies the table range. In the third argument, we give the column number, so that values for that column are returned. Finally, the last argument is set to TRUE. This will allow the VLOOKUP function to find an approximate match for the value.

After entering the formula, press enter. The value returned, in this case, is 8GB for the price of $1300.

Let’s proceed to the next section and understand how to use VLOOKUP for multiple criteria.

How to Use VLOOKUP for Multiple Criteria?

With a helper column, you can give multiple criteria that the VLOOKUP genetically cannot handle. In our example, we will be calculating the price based on both company and product.

For example, if we have to look up the price of an Apple MacBook Pro, where the company name and product name are in two different columns, we use a helper column. This column will store the concatenated values of both columns.

Follow the steps below to perform VLOOKUP with multiple criteria.

First, right-click on a column header and click on Insert. This will help you insert a column to the left of the Company column. Name it as ‘Company & Product’.

On creating the helper column, enter the formula =C2&”-”&D2. Then, drag the formula down to the rest of the cells in the column.

On creating the concatenated column successfully, we can now look for the value. Here, we look for the price of an Apple MacBook Pro.
So, we enter the formula =VLOOKUP(B15&”-”&C15,B1:G11,5,FALSE)

The first parameter helps to concatenate the lookup value, i.e., B15&”-” &C15. We can also use the concatenate formula, i.e., CONCATENATE(B15,”-“,C15). The second parameter is the table range. The third parameter specifies the column index, which returns a value from that column. The final parameter is FALSE, as we are looking for an Exact Match. On pressing enter, the price will be returned as follows.

Tips to Use VLOOKUP Function Efficiently

When we enter an incorrect formula or add a wrong value somewhere the VLOOKUP function shows an error. Here are some tips that you must keep in mind while using VLOOKUP function –

If you omit range_lookup from the VLOOKUP function, it will evaluate the non-exact matches as well. However, it will use an exact match if it exists in your data.
If the lookup column has duplicate values then it will only pick the first value.
VLOOKUP is not case-sensitive.
Adding a column after applying VLOOKUP formula will show an error as hard-coded index values don’t change automatically on adding or deleting columns.

Common Errors in VLOOKUP Function

Here, You will find simple explanations of the main causes of VLOOKUP errors such as #N/A, #NAME, #REF and #VALUE, as well as their solutions and fixes. We will start with the most obvious reasons why VLOOKUP is not working, so it might be a good idea to check out the below troubleshooting steps in order.

#N/A error in VLOOKUP

In VLOOKUP formulas, the #N/A error message (meaning “not available”) is displayed when Excel cannot find a lookup value. There can be several reasons why that may happen.

1. The lookup value is misspelt

It’s always a good idea to check the most obvious thing first : ) Misprints frequently occur when you are working with really large data sets consisting of thousands of rows, or when a lookup value is typed directly in the formula.

2. #N/A in approximate match VLOOKUP

If your formula looks up the closest match, (range_lookup argument set to TRUE or omitted), the #N/A error can appear in two cases:

The lookup value is smaller than the smallest value in the lookup array.
The lookup column is not sorted in ascending order.

3. #N/A in exact match VLOOKUP

If you are searching for exact match (range_lookup argument set to FALSE), the #N/A error occurs when a value exactly equal to the lookup value is not found. For more information, see VLOOKUP exact match vs. approximate match.

4. The lookup column is not the leftmost column of the table array

One of the most significant limitations of Excel VLOOKUP is that it cannot look to its left. Consequently, a lookup column should always be the leftmost column in the table array. In practice, we often forget about this and end up with #N/A errors.

Solution: If it is not possible to restructure your data so that the lookup column is the left-most column, you can use the INDEX and MATCH functions together as an alternative to VLOOKUP. Here’s a formula example: INDEX MATCH formula to look up values to left.

5. Numbers are formatted as text

Another common source #N/A errors in VLOOKUP formulas is numbers formatted as text, either in the main or lookup table.

This usually occurs when you import data from some external database or if you’ve typed an apostrophe before a number to show leading zeros.

Here are the most obvious indicators of numbers formatted as text:

Solution: Select all the problematic numbers, click on the error icon and choose Convert to Number from the context menu. For more information, please see How to convert text to number in Excel.

6. Leading or trailing spaces

This is the least obvious cause of the VLOOKUP #N/A error because a human eye can hardly spot those extra spaces, especially when working with big datasets where most of the entries are below the scroll.

Solution 1: Extra spaces in the lookup value

To ensure the correct work of your VLOOKUP formula, wrap the lookup value in the TRIM function:

=VLOOKUP(TRIM(E1), A2:C10, 2, FALSE)

Solution 2: Extra spaces in the lookup column

If extra spaces occur in the lookup column, there is no easy way to avoid #N/A errors in VLOOKUP. Instead, you can use a combination of INDEX, MATCH and TRIM functions as an array formula:

=INDEX(B2:B10, MATCH(TRUE, TRIM(A$2:A$10)=TRIM(E1), 0))

Since this is an array formula, don’t forget to press Ctrl + Shift + Enter to properly complete it (in Excel 365 and Excel 2021, due to support for dynamic arrays, this also works as a regular formula).

Tip. A quick alternative is running the Trim Spaces tool that will eliminate excess spaces both in the lookup and main tables in seconds, making your VLOOKUP formulas error-free.

#VALUE! error in VLOOKUP

In general, Microsoft Excel displays the #VALUE! error if a value used in the formula is of a wrong data type. In respect to VLOOKUP, there are two common sources of the VALUE! error.

1. Lookup value exceeds 255 characters

Please be aware that VLOOKUP cannot look up values containing more than 255 characters. If your lookup values exceed this limit, a #VALUE! error will be displayed:

Solution: Use an INDEX MATCH formula instead. In our case, this formula works perfectly:

=INDEX(B2:B7, MATCH(TRUE, INDEX(A2:A7= E1, 0), 0))

2. Full path to the lookup workbook is not supplied

If you are pulling data from another workbook, you have to include the full path to it. More precisely, you have to enclose the workbook’s name including the extension in [square brackets] and specify the sheet’s name followed by the exclamation mark. If the workbook name or sheet name, or both, contain spaces or any non-alphabetical characters, the path must be enclosed in single quotation marks.

Here’s the structure of the table_array argument to Vlookup from another workbook:

‘[workbook name]sheet name’!range

A real formula might look similar to this:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1′!$B:$D, 3, FALSE)

The above formula will search for the value of A2 in column B of Sheet1 in the New Prices workbook, and return a matching value from column D.

If any element of the path is missing, your VLOOKUP formula won’t work and return the #VALUE error (unless the lookup workbook is currently open).

For more information, please see:

3. The col_index_num argument is less than 1

It’s hard to imagine a situation when someone intentionally enter a number less than 1 to specify the column to return values from. But it may happen if this argument is returned by some other function nested in your VLOOKUP formula.

So, if the col_index_num argument is than 1, your formula will return the #VALUE! error too.

If col_index_num is greater than the number of the columns in the table array, VLOOKUP produces the #REF! error.

#NAME error in VLOOKUP

This is the easiest case – the #NAME? error appears if you’ve accidentally misspelled the function’s name.

The solution is obvious – check the spelling 🙂

The main causes of errors in Excel VLOOKUP

Apart from having a fairly complicated syntax, VLOOKUP has arguably more limitations than any other Excel function. Because of these limitations, a seemingly correct formula may often deliver results different from what you expected. Below you will find solutions for a few typical scenarios when VLOOKUP fails.

VLOOKUP is case-insensitive

The VLOOKUP function does not distinguish the letter case and teats lowercase and uppercase characters as identical.

Solution: Use VLOOKUP, XLOOKUP or INDEX MATCH in combination with the EXACT function that can match text case. You can find the detailed explanations and formula examples in this tutorial: 5 ways to do a case-sensitive Vlookup in Excel.

A new column was inserted or removed from the table

Regrettably, VLOOKUP formulas stop working every time when a new column is deleted from or added to the lookup table. This happens because the syntax of the VLOOKUP function requires defining the index number of the return column. When a new column is added to/removed from the table array, obviously that index number changes.

Solution: The INDEX MATCH formula comes to the rescue again : ) With INDEX MATCH, you specify the lookup and return ranges separately, so you are free to delete or insert as many columns as you want without worrying about updating every associated formula.

Cell references change when copying the formula to other cells

The heading gives an exhaustive explanation of the problem, right?

Solution: Always use absolute references (with the $ sign) for the table_array argument, e.g. $A$2:$C$100 or $A:$C. You can quickly switch between different reference types by pressing the F4 key.

VLOOKUP returns the first found value

As you already know, Excel VLOOKUP returns the first value it finds. However, you can force it to bring the 2^nd, 3^rd, 4^th or any other occurrence you want. There is also a way to get the last match or all found matches.

Why does my VLOOKUP work for some cells but not others?

When your VLOOKUP formula returns the correct data I some cells and #N/A errors in others, there can be a few possible reasons why that happens.

1. The table array is not locked

Suppose you have this formula in row 2 (say in E2), which works nicely:

=VLOOKUP(D2, A2:B10, 2, FALSE)

When copied to row 3, the formula changes to:

=VLOOKUP(D3, A3:B11, 2, FALSE)

Because a relative reference is used for table_array, it changes based on the relative position of the row where the formula is copied, in our case from A2:B10 to A3:B11. So, if the match is in row 2, it won’t be found!

Solution: When using a VLOOKUP formula for more than one cell, always lock the table array reference with the $ sign like $A$2:$B$10.

2. Text values or data types do not match

Another common reason for VLOOKUP failure is the difference between your lookup value and a similar value in the lookup column. In some cases, the difference is so subtle that it’s hard to spot visually.

Solution: When VLOOKUP is returning an #N/A error while you can clearly see the lookup value in the lookup column, and apparently both are spelt exactly the same, the first thing you need to do is to determine the root cause of the problem – the formula or the source data.

To see whether the two values are the same or different, make a direct comparison in this way:

=E1=A4

Where E1 is your lookup value and A4 is an identical value in the lookup column.

If the formula returns FALSE, that means the values differ in some way, though they look absolutely alike.

In case of numeric values, the most possible reason is numbers formatted as text.

In case of text values, most likely the problem is in excess spaces. To verify this, find out the total length of the two strings using the LEN function:

=LEN(E1)

=LEN(A4)

If the resulting numbers are different (like in the screenshot below), then you’ve pinpointed the culprit – extra spaces:

Why does my VLOOKUP pull wrong data?

There could be even more reasons why your VLOOKUP returns a wrong value:

Incorrect search mode. If you want an exact match, be sure to set the range_lookup argument to FALSE. The default is TRUE, so if you omit this argument, VLOOKUP will assume you are looking for an approximate match and search for the closest value that is smaller than the lookup value.
The lookup column is not sorted. For approximate match VLOOKUP (range_lookup set to TRUE) to work correctly, the first column in the table array must be sorted in ascending order, from smallest to largest.
Duplicates in the lookup column. If the lookup column contains two or more duplicate values, VLOOKUP will return the first found match, which may not be the one you expect.
Incorrect return column. Double-check the index number in the 3^rd argument 🙂

How to Vlookup without errors in Excel

If you do not want to intimidate your users with standard Excel error notations, you can display your own user-friendly text instead or return a blank cell if nothing is found. This can be done by using VLOOKUP with IFERROR or IFNA function.

Catch all errors

In Excel 2007 and later, you can use the IFERROR function to check a VLOOKUP formula for errors and return your own text (or an empty string) if any error is detected.

For example:

=IFERROR(VLOOKUP(E1, A2:B10, 2, FALSE), “Oops, something went wrong”)

In Excel 2003 and earlier, you can use the IF ISERROR formula for the same purpose:

=IF(ISERROR(VLOOKUP(E1, A2:B10, 2, FALSE)), “Oops, something went wrong”, VLOOKUP(E1, A2:B10, 2, FALSE))

Handle #N/A errors

To trap only #N/A errors ignoring all other error types, use the IFNA function (in Excel 2013 and higher) or IF ISNA formula (in all versions).