VLOOKUP Not Working – 7 Possible Reasons + Fix! (2024)

The VLOOKUP Function allows you to retrieve information from a vertically arranged table or dataset.

It’s an extremely popular Excel function that automates looking up information from large datasets.

However, there are times when things may not go exactly as planned.

In this tutorial, we are going to look at the common errors you may encounter when using the VLOOKUP Function. We will then show you how to sort these issues out.

So, let’s get started.

VLOOKUP Not Working Due to Leading or Trailing Spaces

Problem: Below I have a dataset containing a range with product names, the corresponding price, and whether the product is available in multiple colors.

In cell I4, we are using a VLOOKUP Function to return the product price when given a product name in cell H4.

We can see, however, that we are getting a #N/A error.

Now, it’s not clear at first, but our error is due to a space after the word charger in cell H4.

Solution: So to fix this, enter the following formula in cell I4.

=VLOOKUP(TRIM(H4),B3:D16,2,FALSE)

The TRIM Function removes all leading and trailing spaces and ensures that there is only a single space between words.

In this case, the user will enter their product name of choice, and accidentally typing an extra space is a common mistake.

Thus we are catering to this scenario by incorporating the TRIM Function in our VLOOKUP formula and removing all leading and trailing spaces from our lookup value.

This is in order for VLOOKUP to correctly identify the lookup value in the source table, regardless of whether or not the user types an extra space when entering the product name.

Also read: How to Remove Leading Zeros in Excel (3 Easy Ways)

VLOOKUP Not Working Due to Looking up Values to the Left

Problem: Below I have a dataset containing a range of product IDs, product names, and the corresponding price.

In cell I4, we are using a VLOOKUP Function to return the product ID, when given a product name in cell H4. We are getting the #N/A error.

Solution: We have two options that we can use to address this error.

• We can use the INDEX and MATCH combination formula, to perform this lookup. This will work in older and newer versions of Microsoft Excel.

So in cell I4, enter the following formula.

=INDEX(B3:B16,MATCH(H4,C3:C16,0))

• If you are using a newer version of Excel, then you can use the XLOOKUP Function.

So in cell I4, enter the following formula.

=XLOOKUP(H4,C3:C16,B3:B16,,0)

One of the main limitations of the VLOOKUP function is that it cannot retrieve values to the left of the lookup value.

Using the INDEX and MATCH combination is one way to address the problem if you have to look up values to the left.

While the INDEX and MATCH formula is a little bit more complicated to understand, it can retrieve values to the left and right of the lookup value and is not broken if a column is inserted or deleted.

The INDEX Function on its own is used to return a specific value in an array. Whereas the MATCH function is used to return a position of a value in a given range.

So the INDEX and MATCH combination uses the MATCH function to return the needed position based on where Wireless Charger is, in its column, and then the INDEX function uses this position to locate the product ID.

If you have a newer version of Microsoft Excel then consider using XLOOKUP.

This function was developed by Microsoft to address many of the limitations of the standard VLOOKUP function, including looking up values to the left.

Also read: Formulas Not Copying Down in Excel – How to Fix!

VLOOKUP Not Working Due to Incorrect External Reference

Problem: In the example below I have two sheets. The first sheet is called Product Prices, which contains a list of products and their corresponding prices.

The second sheet is called Lookup.

In this sheet, a product name is entered in cell A2, and then VLOOKUP is used in cell B2 to retrieve the corresponding price.

This is sourced from the table on the Product Prices sheet. We are getting a #NAME? error on the Lookup Sheet.

Solution: So to fix this, enter the following formula in cell B2.

=VLOOKUP(A2,'Product Prices'!A2:B10,2,FALSE)

You can use VLOOKUP to retrieve values in tables on other sheets or workbooks. However, you must ensure that the external reference is correct.

In our source example, the Product Prices sheet is not surrounded by quotation marks so that is why we are getting a #NAME? error.

When referencing workbooks ensure that you put the full path to the workbook and the sheet containing the table in the other workbook.

VLOOKUP Not Working Due to Misspelled Words

Below I have a dataset containing a range with product names, the corresponding price, and whether the product is available in multiple colors.

In cell I4, we are using a VLOOKUP Function to return the product price when given a product name in cell H4.

We can see, however, that we are getting a #N/A error. This is due to the fact that the word hybrid is spelled incorrectly in cell H4.

Solution:

1. Select cell H4 and press the F7 key on your keyboard. You should see the Spelling Window.

2. Ensure that the first suggestion is highlighted and click the Change Button. You will be asked if you want to Continue the Spell check, in this case, click No.

See Also

Use IFERROR with VLOOKUP to Get Rid of #N/A Errors

Excel has a spell checker that works similar to Microsoft Word.

It’s advisable to check the spelling of your cell or entire sheet to avoid #N/A errors due to misspelled words when using VLOOKUP.

VLOOKUP Not Working Due to Unsorted Column For Approximate Match

When you are performing a VLOOKUP using an approximate match, you set the range_lookup value to TRUE.

Your lookup column has to be sorted for this type of VLOOKUP example to work.

So in our example below, we can see that the Sales column is not sorted in ascending order. As a result, the VLOOKUP is not returning the right value.

Solution:

1. Select one cell in the Sales column, in this case, we will select cell B3.

2. Right-click and select Sort. Then choose the Sort Smallest to Largest option.

3. You should see the following.

Generally you will always find yourself using FALSE and an exact match for the range_lookup value.

In the cases where you are using TRUE instead (this will be for things like commission tables, or discount rates) the column that you are looking up values from must be sorted.

Otherwise, your function will return an incorrect value or an error.

VLOOKUP Not Working Due to Numbers Stored as Text

Problem: In our example below, we are getting an #N/A error in cell I4.

There is nothing wrong with our VLOOKUP formula and instead, this error is because we have numbers stored as text in column B.

Solution:

1. So select range B3:B16.

2. Go to the Data Tab. In the Data Tools Group, select the Text to Columns Feature.

3. The Text to Columns Wizard should appear. Click Next.

4. Click Next.

5. Click Finish

6. You should see the following. The correct name is retrieved since the numbers in column B are no longer stored as text.

When importing from a database, for example, you may encounter numbers stored as text in some traditionally numeric columns.

You will usually receive an alert in the form of a small triangle, that when clicked will tell you that the number is stored as text.

This is not always the case, however.

If you suspect that you may have this situation and have checked for all other possible errors, then convert the column to the general format using the Text to Columns feature.

VLOOKUP Not Working Due to Not Locking the Table Reference

Problem: In our example below, initially a VLOOKUP function was used to retrieve the name of the product.

So since the VLOOKUP Function retrieved the correct product name in the first instance.

The most natural thing that most people would do without thinking, is to drag the formula down the column. However, when we do that we get the following.

After dragging the formula down the column, we see the second product name is correct.

Consequently, we are only getting #N/As and this is because the table reference is not fixed.

So as we drag the formula down the column, the table reference is updated. If we select cell E5 and press the F2 key, we will see the following.

Solution. So to fix this, we will select cell E3 and highlight only the table reference then press the F4 key to lock the table reference.

We then drag this formula down the column to get the following.

VLOOKUP Not Working Due to the Value Not Being in the Source Table

Problem: Let’s say we encounter the following situation. We are using VLOOKUP to retrieve an employee name based on a given employee ID.

We get an error because we do not have an employee with an ID of 9998.

Solution: So to fix this enter the following formula in cell I4.

=XLOOKUP(H4,B3:B16,C3:C16,"This employee ID doesn't exist")

The XLOOKUP function allows you to specify what value is returned if a match is not found.

You can use the optional [if_not_found] parameter to specify this. In our case, we provide an informative update telling the user that the employee ID doesn’t exist.

You can use this option if you often have users inputting values that don’t occur in the source data table.

Conclusion

We went through the most common reasons why your VLOOKUP formulas may not be working and how to fix these issues.

You would also notice that many of our solutions suggest using the XLOOKUP formula, which is an improved version of the VLOOKUP formula and takes care of some of the limitations of the VLOOKUP formula.

You will find this tutorial helpful if you often work with VLOOKUP in your worksheets.

Other articles you may also like:

• How to Calculate Tiered Commission in Excel (Using IF/VLOOKUP)
• Excel Shortcuts Not Working – Possible Reasons + How to Fix?
• How to Get the Cell Address Instead Of Value In Excel? (Easy Formulas)
• Excel Not Responding – 10 Ways To Fix It!
• #NUM Error in Excel – How to Fix it?