“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.
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.
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.
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.
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.
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.
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.
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’.
03 Then, click on ‘Please Import Excel File’ and add the locked Excel document.
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.
05 After an attack-type has been selected, the program will start the recovery immediately.
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.