How to Fix Vlookup Not Working in Excel

“VLOOKUP is not working on Excel. I am typing the formula, but it is not calculating at all. Please help me.”

MS Excel is one of the hardcore parts of MS Office. It is complicated to use. Still, there are several self-proclaimed Excel experts available who just don’t always know how to proceed. The VLOOKUP not working with text is such an error; most users have no idea why it is appearing or how to fix it. For more details, read our article below.

Part 1. 6 Reasons Why Your Excel Vlookup is Not Working

Most of the Excel user doesn’t even have the slightest clue about VLOOKUP not working on their computer. After due research, we have compiled a list of 6 reasons for Excel VLOOKUP not working. Check them out below.

1. Not Having ‘Lookup_Value’ in the First Column

If there is no LOOKUP VALUE available from where you are getting data, then VLOOKUP will not work.

excel vlookup not working

2. Counting the Wrong number of Columns for Col_index_number

You should recount the COLUMNS count from the place you are getting your data.

vlookup not working reason

3. Extra Space or Characters

You should also check for extra spaces in the formula. If you are confused about it, then use the TRIM Function to eliminate the extra space.

4. Not Using FALSE for Exact Matching [Range_lookup]

There are times when VLOOKUP works properly, but if you forget to use False or 0 for exact matching and True or 1 for approximate matching, you will have issues.

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

If you forget the absolute reference while copying the formula could cause an issue. So, if you copy and paste the VLOOKUP formula or just drag it, you must put the ‘$’ sign to prevent the column from moving down or side by side. This is known as Absolute Reference.

why excel vlookup not working

6. A New Column Has Been Inserted in the Old Table

In a table, you calculated previously where VLOOKUP was used, and it was working. And it will continue to work unless you insert a new column which can make VLOOKUP unstable.

Part 2. 4 Common Excel Vlookup Errors and How to Fix It

In the previous part of this article, we have discussed the 6 probable reasons VLOOKUP not working in Excel. Now, it is time to look at the common VLOOKUP errors and the steps to fix them.

1. VLOOKUP #NA error

If the VLOOKUP formula is unable to locate a match, this error will appear. This error means ‘not available’.

The Fix: Just check up for any mistakes in the Lookup_Value. Check if the numeric values are formatted as text.

vookup na error

2. VLOOKUP #VALUE error

The #VALUE error can appear for 3 reasons. If the index_number of the VLOOKUP is less than 1 if the path of the workbook is not correct or not complete and it also depends on the length of the value of the Lookup.

The Fix: You can reduce the length of the characters of the Lookup value. The maximum limit is 255 characters in the VLOOKUP function.

vlookup value error

3. VLOOKUP #REF error

If the VLOOKUP you are using has a greater index-number than the number of columns in the table_array then you will have #RFF error.

The Fix: All you have to do is to rectify the index-number that you have supplied.

vlookup ref error

4. VLOOKUP returning incorrect results

If you eliminate the supply match type in the range_lookup argument of a VLOOKUP, then it will start looking for approximate matches if there aren’t any exact values. So, if table_array is not sorted ascending order by the first column, then you will have wrong VLOOKUP returns.

The Fix: You just have to supply a relevant match type in the range_lookup argument of the VLOOKUP. And you must always sort out the table_array in ascending order.

vlookup returning incorrect results

Extra Tips: How to Unlock Excel Worksheet Password

If you have an Excel worksheet, but you are unable to access it as you forgot its password, then we recommend using PassFab for Excel password recovery. This is an excellent and advanced program; it takes only a few minutes for the recovery of Excel password.

  • 01Download and install PassFab for Excel on your computer.
  • 02On the main interface, click on ‘Recover Excel Open Password’.

    recover excel open password in passfab for excel
  • 03 Then, click on ‘Please Import Excel File’ and add the locked Excel document.

    add encrypted excel file in passfab for excel
  • 04 Then, you have to choose an attack type. There are 3 different ones to choose from. Dictionary Attack, Brute Force Attack, and Brute Force with Mask Attack.

    choose an attack type in passfab for excel
  • 05 After an attack-type has been selected, the program will start the recovery immediately.

    password removed successfully in passfab for excel

Summary

Therefore, if the VLOOKUP is not working on the MS Excel, then stop worrying. We have detailed the reasons for the error. It will be easier for you to figure out the problem at your end. Otherwise, you can look for solutions for the specific VLOOKUP errors; it will help you to get rid of the error effectively. If you also have an issue with a locked Excel document and you are unable to access it due to a forgotten password, then use PassFab for Excel and recover the password as fast as possible.

Updated on / Posted by Shirley Howard to Excel Tips / Follow @Shirley Howard

(Click to rate this post)

(0 votes, average: 5.0 out of 5 )

Comment

Product-related questions? Speak directly to our Support Team >>