Fed up of this Excel VLookup not working issue? Do you have any idea why is VLOOKUP not working OR how to fix this?
If not….THEN go through this article as it cover all the reasons of Excel VLOOKUP not working and their fixes.
To recover lost Excel data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Why Is My VLOOKUP Not Working?
In this section, you will get an answer to this very important question why is VLookup not working. So, check out the 7 most common reasons for VLOOKUP not working in Excel 2007/2010/2013/2016/2019.
Have a look…
- VLOOKUP Can’t Find Value
- Table Reference Is Locked
- Column Insertion
- Oversized Table
- VLOOKUP Cannot Look To Its Left
- Not Getting The Exact Match
- Excel Table Has Duplicate Values
Reason 1# VLOOKUP Can’t Find Value
Excel user who all are using VLOOKUP, MATCH, or HLOOKUP functions every now and then encountered some unexpected #N/A error.
Usually, this error encounters when anyone tries to match the Excel lookup value within the array. It’s a clear sign that your Excel function has been failed to fetch lookup value from the lookup array.
What will you do if you will see that the exact matching value is there in the lookup array but your excel application is unable to find that value?
Suppose: if in the shown Excel spreadsheet, lookup function is applied. You want to look up the value “1110004” of the cell to get a match with the value “1110004” present in cell E6.
In this case, if the Excel function fails to fetch the match or showing error #N/A error. The VLOOKUP function is a popular lookup and reference function of Excel. It will show a tricky and dreaded #N/A error message. It may happen because Excel is not considering the two values exactly the same.
Reason 2# Table Reference Is Locked
If you are using multiple VLOOKUPs in excel to catch different information about any particular record. or if in case you are willing to copy off your VLOOKUP to several of your cells then you have to check your table.
The below-shown image shows that the VLOOKUP entered over here is incorrect. The incorrect cell ranges are referenced for the lookup_value and table array.
The table which is used by VLOOKUP function actually searches for and gives information is called astable_array. This is required to get referenced completely in order to copy your VLOOKUP.
So, click on the references which are right inside the formula and after then press the F4 key on your keyboard to modify the reference from relative to absolute. So, enter the formula as=VLOOKUP($H$3,$B$3:$F$11,4,FALSE).
In this example, both the lookup_value and table_array references made were absolute. Basically, it may be the table_array that needs locking.
Reason 3# Column Insertion
The column index number or col_index_num is used by the VLOOKUP function in order to enter information to return a record.
Because this is entered as an index number, and it is not that durable. If a column is put down in the table, then vlookup is not working as this stops VLOOKUP from working. Here is the image is shown below for a scenario.
The whole content is in column 3, but after the insertion of the new column, it became column 4. However, the VLOOKUP has not automatically updated.
One solution you can try to save your worksheet so any other user can’t insert columns. If the user requires to do so, then it’s not a valid solution.
Another option is to insert the MATCH function within the col_index_num argument of VLOOKUP.
The MATCH function can be used to search for something or returning the required column number. This will; makes the col_index_num dynamic so that the inserted columns won’t affect the VLOOKUP.
The formula given below needs to be entered within this example to avoid the above-mentioned problem.
Reason 4# Oversized Table
When more rows get added to the table, VLookup requires to get updated. Below given image shows that VLookup doesn’t check complete table fruit items.
You can consider the formatting range as a dynamic range name or table (Excel 2007+). This process ensures that VLookup will look over the complete of your table.
In order to format the range as a table, choose the cell range that you want to use for table_array. After then tap to the Home > Format as Table and choose any one style from the gallery section. Tap to the Design tab present within Table Tools and then change the table name in the given box.
The VLOOKUP below shows a table named FruitList being used.
Reason 5# VLOOKUP Can’t Look On It’s Left
The limitation of the VLOOKUP function is that it can’t look at its left. This will appear at the left-most column of a table and return information from the right.
The solution to fix the VLOOKUP not working issue is not to use VLOOKUP at all. You can use the combination of INDEX and MATCH Excel function as an alternative for VLOOKUP.
The below given example is to show what information is returned to the left of the column you are looking in.
Reason 6# Not Getting The Exact Match
In the ending argument of VLOOKUP function, well known as range_lookup, you can search for the approximate or an exact match.
In most cases, users look for particular product order, employee or customer and therefore require and an exact match. if you are searching for any unique value, enter FALSE for the range_lookup argument.
This one is optional, but if it is left unfilled then the TRUE value must be used. The TRUE value relies on your data is sorted in order to work.
The Below shown figure will show a VLOOKUP with the range_lookup argument omitted and the incorrect value being returned.
If looking for some unique value then enter FALSE value for the ending argument. The VLOOKUP must be entered like =VLOOKUP(H3,B3: F11,2,FALSE).
This is the first reason behind the VLookup not working in Excel.
Reason 7# Excel Table Has Duplicate Values
Excel VLOOKUP function is limited to return only one record. It gives 1st record which matches the value you are searching for.
If your Excel table is having some duplicates then VLOOKUP fails to perform this task.
In the shown list some duplicates values are present. In this case, VLOOKUP is not the right choice to use. PivotTable is the perfect option to choose the value and then listing out the result.
Below shown table is to shows a list of orders. Suppose you are willing to return the entire order of any specific fruit.
PivotTable allows you to choose the Fruit ID from the report filter and the list of all orders starts appearing.
These are the 7 Best reasons behind VLOOKUP not working error in Excel. Hopefully, now the Excel VLOOKUP won’t work issue is fixed but if not then make use of the automatic solution to fix VLOOKUP won’t working error in Excel.
Automatic Solution: MS Excel Repair Tool
The given manual solution will surely fix off your Excel file issues and errors. but if in case you are caught in any other corruption glitches or damaged file issues then make use of MS Excel Repair Tool. It is the best-suited software for restoring down the corrupt Excel files and also for the retrieval of data from Excel worksheets like cell comments, charts, worksheet properties, and other stuff. It’s a professionally designed program that easily repairs .xls and .xlsx files.
Steps to Utilize MS Excel Repair Tool:
Troubleshooting problems regarding Excel worksheets formula and functions can be a big job. Actually, the error comes with the package. Though Excel offers some auditing tools, which are present under the Formula Auditing group of Formulas tab, so you don’t always need to apply much effort in fixing up these relative issues.
Sometimes just a bit of Excel special knowledge can help you to resolve issues that occurred with such Excel formulas and functions.
I tried my best to provide ample information about the VLOOKUP is not working error in Excel. However, if you are having any additional fixes or any query then please share them with us. You can go to the comment section.