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.

=VLOOKUP(H4,B4:F12,2)

For Leading and Trailing Spaces VLOOKUP not working

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.

For Leading and Trailing Spaces VLOOKUP 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.

=VLOOKUP(TRIM(H4),B4:F12,2)

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.

=VLOOKUP(H4,B4:F12,2)

For Typo Mistake VLOOKUP not working

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.

For Typo Mistake VLOOKUP not 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.

=VLOOKUP(H4,C4:F12,3)

For Numeric Value Formatted as Text VLOOKUP not working

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.

For Numeric Value Formatted as Text VLOOKUP not working

Read More: VLOOKUP Partial Text from a Single Cell in Excel


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.

=VLOOKUP(H4,B4:F12,3)

As Lookup Value is not the Leftmost Column VLOOKUP not working

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.

As Lookup Value is not the Leftmost Column VLOOKUP not working

Read More: VLOOKUP with Two Lookup Values in Excel (2 Approaches)


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.

=VLOOKUP(H4,B4:F12,2,FALSE)

For Oversized Table or Inserting New Row & Column with Value VLOOKUP not working

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.

=VLOOKUP(H4,B4:F14,2,FALSE)

⏩ Another way is converting your dataset into a table.

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

For Oversized Table or Inserting New Row & Column with Value VLOOKUP not working

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.

For Oversized Table or Inserting New Row & Column with Value VLOOKUP not working

Read More: Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)


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.

For Column Index Number Less than 1 VLOOKUP not working

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

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

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.

For Using More than 255 Character VLOOKUP not working

Then, used the following formula

=VLOOKUP(G4,A4:E12,2)

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.

=INDEX($B$4:$B$12,MATCH(TRUE,INDEX($A$4:$A$12=G4,0),0))

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.

For Using More than 255 Character VLOOKUP not working

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


Similar Readings


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.

VLOOKUP Not Working

Solution:

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

Read More: VLOOKUP to Return Multiple Columns in Excel (4 Examples)


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.

For Misspelling Function Name VLOOKUP not working

Solution:

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


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.

=VLOOKUP(H4,B4:F12,2,TRUE)

For Using Approximate Match VLOOKUP not working

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.


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.

For Relative Reference VLOOKUP not working

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

=VLOOKUP(I4,C4:$F$12,2)


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.

For Inserting New Column VLOOKUP not working

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.

=VLOOKUP(I4,B4:G12,MATCH(J3,B3:G3,0),FALSE)

For Inserting New Column VLOOKUP not working

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.


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.

For Lookup Value has Duplicate Values VLOOKUP not working

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.

For Lookup Value has Duplicate Values VLOOKUP not working

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: How to Find Duplicate Values in Excel using VLOOKUP


Practice Section

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

Practice Section VLOOKUP not working


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. please send me some lessons

    tks

Leave a reply

ExcelDemy
Logo