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.

Table of Contents

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.

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

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

=VLOOKUP(TRIM(H4),B3:D16,2,FALSE)
VLOOKUP Not Working – 7 Possible Reasons + Fix! (2)

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.

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

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))
VLOOKUP Not Working – 7 Possible Reasons + Fix! (4)
  • 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)
VLOOKUP Not Working – 7 Possible Reasons + Fix! (5)

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.

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

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

=VLOOKUP(A2,'Product Prices'!A2:B10,2,FALSE)
VLOOKUP Not Working – 7 Possible Reasons + Fix! (8)

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.

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

Solution:

  1. Select cell H4 and press the F7 key on your keyboard. You should see the Spelling Window.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (10)
  1. 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.

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.

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

Solution:

  1. Select one cell in the Sales column, in this case, we will select cell B3.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (13)
  1. Right-click and select Sort. Then choose the Sort Smallest to Largest option.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (14)
  1. You should see the following.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (15)

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.

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

Solution:

  1. So select range B3:B16.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (17)
  1. Go to the Data Tab. In the Data Tools Group, select the Text to Columns Feature.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (18)
  1. The Text to Columns Wizard should appear. Click Next.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (19)
  1. Click Next.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (20)
  1. Click Finish
VLOOKUP Not Working – 7 Possible Reasons + Fix! (21)
  1. You should see the following. The correct name is retrieved since the numbers in column B are no longer stored as text.
VLOOKUP Not Working – 7 Possible Reasons + Fix! (22)

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.

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

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.

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

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.

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

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 – 7 Possible Reasons + Fix! (26)

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.

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

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

=XLOOKUP(H4,B3:B16,C3:C16,"This employee ID doesn't exist")
VLOOKUP Not Working – 7 Possible Reasons + Fix! (28)

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?
VLOOKUP Not Working – 7 Possible Reasons + Fix! (2024)

FAQs

Why is VLOOKUP not working even though formula correct? ›

Check for any errors in your VLOOKUP formula. If there are errors, it can prevent Excel from updating the values automatically. Make sure that your formula references the correct cells and ranges, and that the data you are looking up is in the correct format. Check your security settings.

Why is VLOOKUP not working value? ›

This may be due to a typo in the col_index_num argument, or accidentally specifying a number less than 1 as the index value (a common occurrence if another Excel function nested in the VLOOKUP function returns a number such as "0" as the col_index_num argument).

Why is my VLOOKUP giving me the wrong answer? ›

VLOOKUP Returns a value but it is incorrect

There are a number of possible reasons for this: You didn't specify whether Excel must do an Exact or Approximate match (true or false as the last criteria) You specified that Excel does an approximate match but you should have required an exact match.

Why is the lookup not working? ›

If there is no corresponding entry in the lookup table, the value will return an error. It's working the way it should. If there is a minor discrepancy between the value and the “target” entry in the lookup table, it will return an error. It's working the way it should.

Why does VLOOKUP fail sometimes? ›

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.

What is the alternative to VLOOKUP? ›

The superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH. array is range of cells or an array constant.

What stops VLOOKUP from working? ›

One constraint of VLOOKUP is that it can only look for values on the left-most column in the table array. If your lookup value is not in the first column of the array, you will see the #N/A error.

Why is my VLOOKUP not matching correctly? ›

Your Table Contains Duplicates. The VLOOKUP function can only return one record. It will return the first record that matches the value you looked for. If your table contains duplicates then VLOOKUP will not be up to the task.

What are the 5 common errors in Microsoft Excel? ›

This tool identifies common errors such as DIV/0!, #N/A, or #VALUE! and offers possible solutions. Additionally, you can utilise the 'IFERROR' function to handle errors and display custom messages. What are the five types of Excel errors? The five types of Excel errors are: #DIV/0!, #N/A, #NAME?, #NULL!, and #NUM!.

What are the two main causes of errors for VLOOKUP? ›

5 Common Excel VLOOKUP Errors and How to Avoid Them
  • Lookup Value in the Wrong Column. ...
  • Incorrect Number Formatting. ...
  • Wrong Value Returned Due to Similar Values. ...
  • Not Locking the Data Range. ...
  • SUM Formula Not Working Due to #N/A Values.
Mar 23, 2023

Why does VLOOKUP miss some values? ›

Sometimes an #N/A result is returned just because the VLOOKUP formula's syntax is incorrect. One example could be that the lookup value is in the wrong order in the parameter list. Make sure that the value you are searching for is in the leftmost or first parameter in the formula.

What are the limitations of VLOOKUP? ›

Limitations of VLOOKUP in Excel

Only searches vertically: VLOOKUP can only search for a value in the leftmost column of a table and retrieve a corresponding value from a column to its right. It cannot search horizontally. Limited to exact or approximate matches: VLOOKUP can only perform exact or approximate matches.

What is the value error in VLOOKUP? ›

Additionally, users may encounter the “#VALUE!” error, which indicates that the input arguments in the VLOOKUP function are of the wrong data type or format.

Why is my VLOOKUP not working #ref? ›

The #REF! error shows when a formula refers to a cell that's not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over.

Why isn't my VLOOKUP working when value exists? ›

Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.

Why is VLOOKUP showing the formula and not the result? ›

There are two main reasons you might see a formula instead of a result: You accidentally enabled Show Formulas. Excel thinks your formula is text.

What is the error if VLOOKUP is not found? ›

For example, your lookup value doesn't exist in the source data. In this case there is no “Banana” listed in the lookup table, so VLOOKUP returns a #N/A error. Solution: Either make sure that the lookup value exists in the source data, or use an error handler such as IFERROR in the formula.

Why is my VLOOKUP returning ref when value exists? ›

The #REF! error shows when a formula refers to a cell that's not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over.

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Prof. An Powlowski

Last Updated:

Views: 6287

Rating: 4.3 / 5 (44 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Prof. An Powlowski

Birthday: 1992-09-29

Address: Apt. 994 8891 Orval Hill, Brittnyburgh, AZ 41023-0398

Phone: +26417467956738

Job: District Marketing Strategist

Hobby: Embroidery, Bodybuilding, Motor sports, Amateur radio, Wood carving, Whittling, Air sports

Introduction: My name is Prof. An Powlowski, I am a charming, helpful, attractive, good, graceful, thoughtful, vast person who loves writing and wants to share my knowledge and understanding with you.