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 is found in formulas tab in the Formula Auditing group, you don’t always need so much power to fix these relative issues. Sometimes just a bit of special knowledge can help you to resolve issue occurred with such Excel formulas and functions. This tutorial explains how you can quickly cope with Excel 2013, 2010, 2007 and 2003, “VLOOKUP not working issue. So, that you can easily troubleshoot and fix common errors and overcome VLOOKUP’s limitations also.
Users of the VLOOKUP, HLOOKUP or MATCH functions sometimes get an unexpected #N/A error when they try to match a lookup value within an array. This error generally indicates that the function has actually failed to find the lookup value within the lookup array. But what if you can see that the matching value is present there in the lookup array but Excel still doesn’t find this value?
If your function fails to find this match (signaled by the #N/A error), this may be because Excel does not consider the two values to be exactly equal. You can test for this problem via the following steps:
The VLOOKUP function is the most popular lookup and reference function in Excel. It throws one of the trickiest and dreaded #N/A error message.
The article will look at the 5 most common reasons why vlookup not working in Excel.
Have a look…
- You Need an Exact Match
- Lock the Table Reference
- A Column Has Been Inserted
- The Table has got Bigger
- VLOOKUP Cannot Look to its Left
1. You Need an Exact Match
The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match.
In most cases, people are looking for a particular product order, employee or customer and therefore requires and an exact match. When looking for a unique value, FALSE should be entered for the range_lookup argument.
This one is optional, but if it is left unfilled then the TRUE value is used. The TRUE value relies on your data is sorted in order to work.
The Below shown figure shows a VLOOKUP with the range_lookup argument omitted and the incorrect value being returned.
If looking for some unique value then enter FALSE for the last argument. The VLOOKUP above should be entered as=VLOOKUP(H3,B3: F11,2,FALSE).
This is the first reason behind the vlookup not working in Excel.
2. Lock the Table Reference
If you are using multiple VLOOKUPs to return different information about a record or if you are planning to copy your VLOOKUP to multiple cells then you need to look your table.
The image below shows a VLOOKUP entered incorrectly. The wrong cell ranges are being referenced for the lookup_value and table array.
The table which is used by VLOOKUP function uses to look for and return information from is known as the table_array. This will need to be referenced absolutely to copy your VLOOKUP.
So, click on the references which are right within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. The formula should be entered as=VLOOKUP($H$3,$B$3:$F$11,4,FALSE).
In this example, both the lookup_value and table_array references were made absolute. Typically it might be just the table_array that needs locking.
3. A Column Has Been Inserted
The column index number or col_index_num that is used by the VLOOKUP function in order to enter information to return a record.
Because this is entered as an index number, it is not very durable. If a column is inserted into the table, then vlookup is not working as this stop VLOOKUP from working. The image below shows 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 protect the worksheet so that user can’t insert columns. If the user will need it to do this, then it is not a viable solution.
Another option would be to insert the MATCH function into the col_index_num argument of VLOOKUP.
The MATCH function can be used to look for and return the required column number. This makes the col_index_num dynamic so inserted columns will no longer affect the VLOOKUP.
The formula below could be entered in this example to prevent the problem discussed above.
4. The Table has got Bigger
As more rows are added to the table the VLOOKUP needs to be updated in order to ensure that these extra rows are included. The image below shows a VLOOKUP that doesn’t check the entire table for the item fruit.
To fix VLOOKUP not working issue it is considered formatting the range as a table or as a dynamic range name. These techniques will ensure that your VLOOKUP function will always check the entire table.
In order to format the table, select the range of cells up to which you want to use for the table_array and click Home > Format as Table and select a style from the gallery. Click the Design tab under Table Tools and change the table name in the box provided.
The VLOOKUP below shows a table named FruitList being used.
5. VLOOKUP Cannot Look to its Left
Limitation of the VLOOKUP function is that it can’t look at its left. It will look at the left most column of a table and return information from the right.
Solution to fix the vlookup not working issue is not to use VLOOKUP at all. Using a combination of the INDEX and MATCH functions of Excel is a common alternative to VLOOKUP. It is far more versatile.
The Below mentioned example shows it is being used to return information to the left of the column you are looking in.
These are the 5 Best reason behind VLOOKUP not working error in Excel. Hope now the Excel VLOOKUP not working error is fixed but if not then makes use of the automatic solution to fix the VLOOKUP not working error in Excel
Automatic Solution: MS Excel Repair Tool
The above mentioned manual solution will most probably sort out the issues and mentioned errors from Excel file. But for any other corruption or file damage most suitable option would be to make use of MS Excel Repair Tool. This is is highly competent in restoring corrupt Excel files and also retrieves data from worksheet like cell comments, charts, other data and worksheet properties. This is a professionally designed program that can easily repair .xls and .xlsx files.
Steps to Utilize MS Excel Repair Tool:
I tried my best to provide the 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 it with us. You can go to the comment section or on MS Excel Ask Question page.