VLOOKUP Not Working (8 Reasons & Solutions)

VLOOKUP is one of the most common and useful functions. As it is widely used so many people have complaints about not working VLOOKUP correctly or showing incorrect results. Though VLOOKUP has some limitations yet most of the error we get is for not understanding the syntax properly or not carefully using it. In this article, I’ll explain why VLOOKUP not working.

To make the explanation understandable I’m going to use a dataset that represents product information about a particular fruit shop. There are 5 columns in the dataset; these are Fruit, Order ID, Quantity (Kg), Price, and Order Date.

Download to Practice

Feel free to download the workbook from the link below.

8 Reasons of VLOOKUP Not Working

1. VLOOKUP Not Working and Showing N/A Error

In this section, I will show you why the #N/A error occurs while working with the VLOOKUP function. As well as I will suggest you the best solution to avoid #N/A error.

1.1. Leading and Trailing Spaces 

In a large datasheet, the possibility of having extra spaces is common. Also, it is hard to identify the error as you won’t get the error unless you go through the dataset carefully.

Here, I applied the VLOOKUP formula correctly.

First, select a cell to place your resultant value.
➤ I selected cell I4
Then, type the following formula into the Formula Bar.

Here, in the VLOOKUP function, I selected the cell H4 as lookup_value, and selected the range B4:F12 as table_array. As I want to know the Order ID so given 2 as col_index_num.

Press the ENTER key. Now, you were supposed to get the Order ID of the look_up value but it will show #N/A.

Now, after looking at the dataset you will find that the lookup_value Apple has some leading spaces that’s why VLOOKUP is not working.

Solution:

To remove extra leading or trailing spaces, use the lookup_value argument with the TRIM function within the VLOOKUP function.

Let me show you how you can use the TRIM function within the VLOOKUP function.
To avoid the VLOOKUP error type the following formula in your selected cell.

Here, the TRIM function will remove all existing leading and trailing spaces of the selected cell H4.

1.2. For Typo Mistake VLOOKUP Not Working

Typing mistake of lookup_value is another reason for not working VLOOKUP.

Here, you will see I inserted the formula correctly in the selected cell.

Press the ENTER key but instead of showing Order ID, it will show you a #N/A error.

Now, look at the lookup_value you will see that the spelling of Apple is incorrect, that is the reason VLOOKUP is not working.

Solution:

Always carefully type the lookup_value. You have to maintain the exact spelling of the value from the data table.

As I typed the lookup_value as it is in the table so VLOOKUP is working.

1.3. Numeric Value Formatted as Text

In case the numeric values are formatted as text in a table_array then it will show you a #N/A error while using the VLOOKUP function.

I will try to get the Price by using the Order ID as lookup_value.

First, select a cell to place your resultant value.
➤ I selected cell I4
Then, type the following formula into the Formula Bar.

Press the ENTER key. Thus, you will get the #N/A error instead of Price.

Now, if you go through the Order ID column then you will see the number 1001 is formatted as text. That’s the reason for not working VLOOKUP.

Solution:

To avoid such types of errors, always check the format of the numeric values. Here, I corrected the numeric format as number so the VLOOKUP is working.

Read More: VLOOKUP with Numbers in Excel (4 Examples)

1.4. Lookup Value is Not the Leftmost Column

The VLOOKUP function maintains a sequence, which is the lookup_value has to be the leftmost column, if not then it won’t work.

I will try to get the Price by using the Order ID as lookup_value.

So, I used the following formula.

But here the Order ID column is not the leftmost column of the table_array B4:F12 that’s why it is showing #N/A error.

Solution:

Here you can avoid the error in 2 ways.

⏩ One is you can change the table_array where the lookup_value will be the leftmost column.

⏩ Second, you can place the lookup_value column at the leftmost position of the dataset table.

1.5. Oversized Table or Inserting New Row & Column with Value

Sometimes we insert new data into our dataset but forget to change the table_array then the VLOOKUP can’t work properly.

I will try to get the Order ID by using the Fruit as lookup_value.

So, I used the following formula.

Here, I used the exact match type to avoid misleading information and also inserted information for Lichi yet got an error because I didn’t update the table_array.

Solution:

Remember whenever you insert new data to your dataset table update the table-array also.

⏩ Here, I updated the table_array in the formula.

⏩ Another way is converting your dataset into a table.

First, select the cell range.
Then, open Insert >> select Table

A dialog box will pop up.
Then, click OK.

As your dataset is now turned into a table you just can use the table name.

Read More: How to Use VLOOKUP Function with Exact Match in Excel

2. VLOOKUP Not Working and Showing VALUE Error

From this section, you will get to know why the #VALUE error occurs while working with the VLOOKUP function. Also, I will suggest you the all-possible solution to avoid #VALUE error.

2.1. For Column Index Number Less than 1

If you mistakenly use the col_index_num less than 1, then you will get #VALUE error.

In case you get this #VALUE kindly check your col_index_num argument.

Read More: How to Use Column Index Number Effectively in Excel VLOOKUP

2.2. Using More than 255 Character

Suppose you have a long text as a value which is more than 255 characters then you will have #VALUE error.

Here, in the A7 cell, I inserted a value exceeding 255 characters.

Then, used the following formula

Now, you can see that the result is showing the #VALUE error.

Solution:

To avoid this error either you can reduce the character or you can use the INDEX and the MATCH functions instead of VLOOKUP.

Here, I used the MATCH and the INDEX function.

Here, in the INDEX function selected the absolute reference of the cell range $B$4:$B$12 from where I want to return value.

In the MATCH function, given TRUE as lookup_value and used another INDEX($A$4:$A$12=G4,0) function as lookup_array then used taken 0 as match_type to use Exact Match.

Press the ENTER key and you will get the result for the lookup_value of more than 255 characters.

Read More: INDEX MATCH vs VLOOKUP Function (9 Practical Examples)

3. VLOOKUP Not Working and Showing REF Error

Here, you will know why the #REF error occurs while working with the VLOOKUP function and also, you’ll get the solution to avoid #REF error.

3.1. Using Column Index Number Greater Than Table

In case you use the col_index_num greater than the number of columns you have in the table_array then you will get #REF error.

Here, I’ve used 6 as col_index_number but the table_array has 5 columns in total that’s why the VLOOKUP function is not working and showing #REF error.

Solution:

To avoid #REF error checks the col_index_num and use the number which is in the table_array.

Read More: Perform VLOOKUP by Using Column Index Number from Another Sheet

4. VLOOKUP NAME Error

Let me show you why the #NAME error occurs and how you can remove it.

4.1. For Misspelling Function Name VLOOKUP Not Working

The #NAME error comes for the misspelling of functions name.

Solution:

To avoid #NAME errors always use the appropriate function name from the Excel built-in function.

Similar Readings

• VLOOKUP from Multiple Columns with Only One Return in Excel
• How to Vlookup with Multiple Matches in Excel (with Easy Steps)
• VLOOKUP and Return All Matches in Excel (7 Ways)
• How to Use VLOOKUP in VBA in Excel (4 Ways)
• VLOOKUP with Drop Down List in Excel

5. Using Approximate Match

If you use approximate match (TRUE) then there stays a possibility of either #N/A error or incorrect result.

I will try to get the Order ID by using the Fruit as lookup_value.

So, I used the following formula.

But here, I gave Lichi as lookup_value and used TRUE as range_lookup. VLOOKUP shows 1007 as Order ID which is incorrect because 1007 is the Order ID of Cherry.

As I used approximate match so instead of showing error it shows wrong information

Solution:

Use the lookup_value carefully. Instead of using approximate match type you can use exact match type. I think getting an error is way better than having misleading information.

You can wrap up the formula with the IFERROR function to show any error message when it can’t find the value within the range.

Read More: 10 Best Practices with VLOOKUP in Excel

6. Table Reference Is Relative

If your table array is referenced relatively then you may have error notification or error while copying the formula to lookup other values.

Solution:

To avoid this error use absolute reference.
Press the F4 key while selecting the reference then it will convert the relative reference to absolute reference.

Here, I used the following formula

Read More: How to Copy VLOOKUP Formula in Excel (7 Easy Methods)

7. VLOOKUP Not Working For Inserting New Column 

If you insert a new column to your existing dataset then the VLOOKUP function doesn’t work. The col_index-num is used to return information about a record in the VLOOKUP function. The col_index-num is not durable so if you insert a new one then the VLOOKUP won’t work.

Here, you can see the VLOOKUP function is working properly.

But here I inserted one new column that’s why it is showing 0 instead of showing the expected result.

Solution:

⏩ To avoid such types of problems either you can protect the worksheet so that anyone can’t insert new columns but it’s not friendly enough.

⏩ Another solution is you can use the MATCH function within the VLOOKUP function.

So, type the following formula.

Here, in the VLOOKUP function, I selected the cell I4 as lookup_value then selected the range B4:G12 as table_array and as the col_index_num used the MATCH function, also used FALSE as range_lookup to get Exact Match.

In the MATCH function, I used the column name J3 as lookup_value, next selected the column name range B3:G3 as lookup_array then taken 0 as match_type to use Exact Match.

Press the ENTER key. Thus, you will get the expected result you want.

Read More: How to Find Column Index Number in Excel VLOOKUP (2 Ways)

8. Lookup Value Has Duplicate Values

In case your lookup_value contains duplicate values then VLOOKUP won’t work for all the available values.

VLOOKUP only returns the first value that matches the value you looked for.

Solution:

To avoid such types of problems either you can remove the duplicates or you can use the pivot table.

⏩ You can remove the duplicates using the Remove Duplicates from then Ribbon.

⏩ Also, you can use the Pivot Table.

To use it,
First, select the cell range
Then, open the Insert tab >> select Pivot Table

A dialog box will pop up, select the place then click OK.

Now, you can select the Fruit and Order ID in Rows then it will show the existing Order ID of your selected Fruit.

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways.

Conclusion

In this article, I tried to cover all types of scenarios of VLOOKUP not working along with the solution to avoid errors. These different ways will help you to work with the VLOOKUP function more efficiently and easily. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• How to Apply Double VLOOKUP in Excel (4 Quick Ways)
• VLOOKUP with Two Lookup Values in Excel (3 Simple Methods)
• How to Make VLOOKUP Case Sensitive in Excel (4 Methods)
• Use VLOOKUP for Multiple Columns in Excel (6 Examples)
• How to Use Dynamic VLOOKUP in Excel (3 Easy Ways)
• Use VLOOKUP Function in Excel VBA (4 Examples)
• How to Hide VLOOKUP Source Data in Excel (5 Easy Ways)

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel