How to fix Vlookup not working in Excel

Vlookup is a great way to search data based on a search key in large data sets. However, if we don’t set up things in the correct way then we can encounter errors while implementing Vlookup. 

There can be a number of reasons for the vlookup to not work in spreadsheets. Let’s see how it can be a problem in excel and how to resolve it. We may encounter the following error types.

• Error 1 –  #REF!
• Error 2 –  #N/A

Error 1 –  #REF!

We have data of students in various departments in a university and the number of failures as shown. We need to get the number of failures in cell C10 against the department in cell C9. However, the Vlookup gives a #REF! error.

Step 1 – Inspecting the function for #REF!

• Let us take a look at the function in cell C10.
• There are 3 important parameters of Vlookup, lookup_value, table_array, col_index_num. Each of them should be setup properly to avoid any errors.

• In our formula, the lookup_value portion correctly refers to the cell C9 which lists one of the names of the Department i.e. Management. 
• The table_array has to point to the range which contains the lookup value up and it should span to at least the column containing the corresponding information which we wish to get when our lookup_value is found. So our formula currently points to the range B1:D4 which satisfies both conditions i.e. it contains both the lookup_value and the corresponding value to be searched.
• However, in the col_index_num attribute the column index number is incorrect as we have a total of 3 columns in our selected range and we are referring to column number 4 and that is the source of the error.

Step 2 – Correcting the #REF! error

• So, now that we have found our error we are going to rectify our mistake and type 3 instead of 4 in the col_index_num attribute. After this, the Vlookup works successfully and evaluates to the desired value.

Error 2: #N/A

We have the same dataset as before but this time the error is different i.e. the #N/A error. 

Step 1 – Inspecting the function for #N/A

• Let us take a look at the function this time.
• The lookup_value is correctly pointing at C9 i.e. Engineering, which is available in the data range.
• The range is from A2 to D4 which is incorrect. This is because the biggest limitation of VLOOKUP is that the lookup value must be in the first column of the table range and only then it can evaluate the columns to the right of the first column containing the lookup value.
• In our case the table range starts one column left of the column containing the lookup value, therefore, we got this #N/A error.

Step 2 – Correcting the #N/A error

• So let’s correct the range from A2:D4 to B2:D4 and press enter.
• You now have the correct value in the cell C10.