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, but 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 problems” in 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 tries 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 their in the lookup array but excel still doesn’t find this value?
If your function fails to find this match (signalled 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 most 6 most common reasons why your VLOOKUP is not working.
- 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
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 actually but if it is left unfilled then the TRUE value is used. The TRUE value relies on your data being sorted in order to work.
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).
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 needs 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.
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 about a record.
Because this is entered as an index number, it is not very durable. If a column is inserted into the table, it could stop your 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 user will need ti able 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.
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 this issue consider 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.
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 this involves not using 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.
Below mentioned example shows it is being used to return information to the left of the column you are looking in.