6 Reasons why VLOOKUP is not working

VLOOKUP is not working !!! 

Don’t need to get panic. I have seen many persons complaining VLOOKUP not working or VLOOKUP not working with numbers or VLOOKUP not working properly or VLOOKUP not working between sheets or VLOOKUP not working for some cells etc.

You have tried a lot to fix it but all in vain. 

Calm down, there are very few reasons behind not working of VLOOKUP function.

In this section I will show you step by step how to detect where is the problem for not working of VLOOKUP. 

But before going I would like to review the SYNTAX of VLOOKUP once again.
=VLOOKUP( lookup_value, table or array, col_index_number, [range_lookup] ) 

Below is the few Check List to check why VLOOKUP is not working

1. Not Having 'Lookup_Value' in First Column :

If there is no LOOKUP VALUE (which you are searching for, the first parameter) is not available in the table or array from where you are getting or matching data, VLOOKUP will not work. It will display #N/A error.

Please watch carefully the below image. Here I want to match the employee column in the second table in the small table from the large table to get Sales Quantity of the given employees. Here I am getting #N/A error in the first row because “Employee 14” is not present in the first table.

This is a small example, therefore, what you need to do is search manually for the Lookup Value by pressing Ctrl+F in your Keyboard.

If Value is not found in the first table, then it’s OK, use IFERROR to mask the #N/A error.

2. Counting the Wrong Number of Columns for Col_index_number

Please recount the COLUMNS count from where you are fetching data, the first table

In this example I wrongly count the column number as 4 instead of  3 
=VLOOKUP(F4,B3:D14,4,0)

3. Extra Space or Characters

Please check for Extra spaces in your formula, it you have doubts use TRIM function to remove extra spaces. To learn more about TRIM click this.

4. Not Using FALSE for Exact Matching [Range_Lookup]

Although, sometimes VLOOKUP works perfectly but do not forget to use [Range_Lookup], False or 0 for exact matching and True or 1 for approximate matching. 

Its always better to make a good habit.

5. Forgetting Absolute References (F4) When Copying the Formula

While copy and paste VLOOKUP formula or just dragging the VLOOKUP formula you must put $ sign to restrict the column not to move down or side by side. This is called Absolute reference. 

There are two ways to do this, one you insert a $ sign manually or you can use F4 (Function Key) key. 

6. A new column has been inserted in the old table

In a previously calculated table where VLOOKUP has been used which was working, inserting a new column will create VLOKKUP unstable. 

To resolve this either you can recheck your VLOOKUP formula or you can use an additional function MATCH to be nested with VLOOKUP. Using MATCH with VLOOKUP will make your formula very dynamic.

Please click here to learn how to use MATCH function with VLOOKUP.


Hope your problem is resolved now. If not please mention your problem in the comment box.

Thank you for reading...