Failure to Look Up Values in Excel #N/A – Lookup Failure Error Users of the VLOOKUP, HLOOKUPor MATCHfunctions sometimes get an unexpected #N/A error when they attempt to look up a value within an array. This error generally indicates that the function has failed to find the lookup value within the lookup array.
For example, if a lookup function was used in the spreadsheet below, the user might expect the lookup value “1110004” in cell B1 to match the value “1110004” in cell E6. 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 exactlyequal.
How to fix VLOOKUP not working in Excel?
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 wrong cell ranges are being referenced for the lookup_value and table array. The table that the 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.
Why does VLOOKUP return na error?
There could be some reasons why V LOOKUP returns this error. This is one of the most common reasons behind the #NA error in VLOOKUP. In big data set it is very hard to identify these leading or trailing spaces in lookup values that cause the VLOOKUP function to not find the match and return #NA error.
Some authors claimed in case of Approximate Match type In case of approximate match type (TRUE), your VLOOKUP function generates # NA error if your lookup value is smaller than the smallest value available in the first column of table_array. Generally, if you enter wrong data type in the formula in Excel, then formula generates #Value error.
A limitation of the VLOOKUP function is that it cannot look to its left. It will look down the leftmost column of a table and return information from the right. The 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.
Why can’t I find my formula in Excel?
The reasons why Excel may not find your search has already been addressed above, which includes mistyping of lookup values, leading and trailing spaces and sometimes when numbers are formatted as text and vice versa. Now let’s look at the some other VLOOKUP errors that may occur when writing formulas. This error is normally caused by a typo.