Wondering why VLOOKUP returns #NA when match exists in Excel and how you can fix it? We’ve got you covered.
The VLOOKUP function is a valuable tool in spreadsheets that lets you find a specific value in one column and then see a related value from a different column. However, using this function can be challenging because it has a lot of complicated rules you need to follow. If you don’t get these rules right, you might have errors. A standard error is seeing #N/A on your screen, which means the function couldn’t find the value you were looking for, even though it’s there.
To help solve this problem, we’ve figured out five main reasons why this error might happen. For each of these reasons, we’ve also devised ways to fix them.
What is an #N/A Error in Excel?
The #N/A error is like a sign that says “value not found.” It appears when you’re using the VLOOKUP function in a spreadsheet, and it can’t find the data it’s supposed to. Imagine it’s like looking for a book in a library, but it isn’t there. This error tells you that the function tried to find your value but wasn’t available in your data.
How to fix the VLOOKUP returns #NA when match exists issue
Throughout this article, we’ll use an example dataset of a product price list to illustrate each point. Let’s take a quick preview of this dataset:
Reason 1: Exact match not found
If the value you’re searching for doesn’t exactly match the value in your dataset, you’ll get the #N/A error again.
For example, in the image below, we’ve entered ‘Lipstick’ as our lookup value in cell C14. However, in the Second column of our dataset, the word isn’t ‘Lipstick’ but ‘Lipsticks.’ Because of this slight difference in wording, the #N/A error shows up in cell C15.
Solution
Pay attention to the lookup value. Make sure you enter it correctly in the search field. If you see a #N/A error, double-check your dataset and adjust the lookup value. In this case, you should type ‘Lipsticks’ instead of ‘Lipstick’ in cell C14.
Reason 2: The lookup value is not present in the first column of the table array argument
The initial argument in the VLOOKUP function is known as the lookup_value. For the function to operate effectively, this lookup_value must be found in the first column of the table array. If this isn’t the case, the VLOOKUP function will result in a #N/A error.
In the following image, we have inserted the formula:
=VLOOKUP(C14, B5:E12, 4, FALSE)
In cell C14; our lookup value is placed in cell C14, which is ‘Novel.’ However, this item ‘Novel’ is located in the second column of our chosen table array, not in the first column where it needs to be for the VLOOKUP to work correctly.
First Solution
Since it’s essential for the lookup value to be in the first column of the table array, one solution is to move the contents of the second column to the first column.
However, this approach might only sometimes be practical. Often, the second column could result from a formula or be linked to other columns. In such scenarios, it’s worth exploring a second solution.
Second Solution
Consider adjusting the table array slightly. Right now, the table array is set from B5:E12. If you start this range from column C instead, making it C5:E12, column C becomes the first column in this newly defined table array. With this change, the VLOOKUP function should work correctly. Remember, since you’ve altered the table array, you must also update the column index number. If you want to retrieve values from the Price column, the new column index will be 3 for the newly specified table array.
Reason 3: Additional spaces in table lookup values
Spaces, being invisible, can be tricky to spot, and they might affect the outcome of the VLOOKUP function.
Take the example shown in the image below. Our lookup value is ‘Novel,’ which also appears in the lookup range within the data table. However, the VLOOKUP function still returns a #N/A error!
Finding the exact cause of this error may be challenging. The issue is an additional space after ‘Novel’ in the Category column.
While it might appear to be a minor issue, it can cause significant problems. Since spaces are invisible and hard to detect, they can be a source of frustration in data accuracy.
Solution
You can manually inspect your data to see if there are any extra spaces. Alternatively, you can use the TRIM function, designed to eliminate all trailing spaces in your data. This function can be a simple and effective solution to ensure accuracy in your VLOOKUP results.
Reason 4: Errors in the lookup_value argument within the VLOOKUP syntax
A minor issue that can cause significant headaches is errors in syntax or typos when inputting the lookup value in the VLOOKUP function.
For example, as shown in the image below, the lookup value is supposed to be in cell C14. However, we accidentally typed C147. This small typo is enough to trigger the #N/A error in the corresponding cell. Such simple mistakes can have significant impacts on the function’s outcome.
Solution
It’s essential to be vigilant about the function’s syntax and avoid any kind of typos. By simply paying attention to these details, you can prevent the occurrence of the #N/A error. This level of carefulness can make a big difference in ensuring your VLOOKUP function works as intended.
Reason 5: Lookup value is less than the minimum value in the table array
A further reason for the VLOOKUP function to yield a #N/A error is when the lookup value is smaller than the lowest values in the lookup range.
For example, in the image shown below, the lookup value entered is 9. However, the smallest value in the lookup range, specifically within the ID column, is 10. Consequently, this discrepancy causes the VLOOKUP function to return a #N/A error.
Solution
Ensure your lookup value isn’t smaller than the smallest value in the range. To avoid the #N/A error, adjust the lookup value from 9 to a number that’s actually in the ID column. Once you make this change, the error should disappear.
Key points to remember
- Ensure that your lookup value is located in the first column of the table array.
- Pay close attention to the syntax when using the VLOOKUP function.
Conclusion
In conclusion, we’ve explored five potential issues and their solutions related to the VLOOKUP function returning a #N/A error in Excel, even when a matching result is present. These insights help troubleshoot and resolve such errors in your spreadsheets.
Learn more about Excel with these helpful guides:
