We use the VLOOKUP (Vertical Lookup) function to search for a value vertically in a column and then return a corresponding value from another column. But one major problem using this function is that the function syntax may seem complicated and also it requires maintaining multiple rules. Which may cause an erroneous result such as throwing #N/A instead of intended values. To get rid of this problem, we’ve discussed 5 different reasons and their solutions that may be the factors behind the VLOOKUP function returns #N/A when a match exists.
What Is #N/A Error?
The #N/A error stands for “value not available”. When you run the VLOOKUP function throughout your dataset but unfortunately the function cannot retrieve the intended result, then the #N/A error is thrown. There might be several problems behind this error; which you will all get to know in the following section of this article.
Why VLOOKUP Returns #N/A When Match Exists:
5 Reasons
In this article, we will be using a sample product price list as a dataset to demonstrate all the reasons. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the problems one by one.
Reason 1: Lookup Value Doesn’t Exist in the First Column of the Table_array Argument
The first argument of the VLOOKUP function is called lookup_value. One of the primary requirements for this function to work properly is that the lookup_value should exist within the first column of the table array. For any exception regarding this rule, the VLOOKUP function will return a #N/A error.
In this following image, we have inserted the formula:
=VLOOKUP($D$14,B5:E12,4,0)Here the lookup value is stored in cell D14, which is karakum. As we can see this item is not present in the first column of the selected table array but in the second column.
As a result of that, we can see that the VLOOKUP function has already thrown the #N/A error.
🔗 Get Solutions
1. First Solution: As the primary requirement about the lookup value is that it should exist within the first column of the table array, so you can transfer the second column to the first column if possible.
But yeah, in many cases this might be quite impractical to swap columns. Because maybe your second column is a result of a formula or it is linked up to other columns as well. So in such cases, you can consider the second solution.
2. Second Solution: Modify the table array a little bit. Currently, the table array is B5:E12. If this range begins from column C instead of column i.e. C5:E12 then column C will be the first column of the newly defined table array. In that case, the VLOOKUP function will function properly. As you have changed the table array, you have to update the column index too. As for the newly assigned table array, to return value from the Price column, the new column index will be 3.
3. Third Solution: You can use the INDEX and MATCH functions in collaboration. Using these two functions can easily eliminate the barrier of existing the lookup value within the first column of the table array.
All you need to do is, simply replace the previous formula with the following formula:
=INDEX(E5:E12,MATCH(D14,C5:C12,0))After inserting this formula you will see that the problem is blown away, as soon as you hit the ENTER button just after inserting the second formula. Boom!
Reason 2: Exact Match Isn’t Found
If the lookup value doesn’t match exactly with the value stored in the dataset, then the #N/A error will appear again.
For instance, in the picture below, we’ve inserted the lookup value in cell D14, which is Cereal. Unfortunately, there is no such word as Cereal in the first column, but cereals. That’s why the #N/A has appeared in cell D15.
🔗 Get Solutions
Be careful about the lookup value. Write down the lookup value correctly within the insertion field. If you receive any #N/A error then recheck your dataset and correct your lookup value accordingly. For this instance, type Cereals instead of Cereal within cell D14.
Reason 3: Lookup Value Is Smaller Than the Smallest Value in the Array
Another reason that might cause the VLOOKUP function to return the #N/A error is the lookup value being smaller than the smallest values in the lookup range.
For instance, in the picture below the lookup value is 200, whereas the smallest value in the lookup range i.e. within the ID column is 207. As a result, the VLOOKUP function returned the #N/A error.
🔗 Get Solutions
Make sure that the lookup value is not smaller than the minimum value stored within the lookup range. Change the lookup value from 200 to anything as listed within the column ID. Then the not available error will eventually go away.
Reason 4: Extra Spaces in Table Lookup Values
Spaces are invisible to us, that’s why they are quite difficult to identify. This reason may affect the return value of the VLOOKUP function.
For example, in the image below our lookup value is Candies. Moreover, this value also exists in the lookup range within the data table. Nevertheless, the VLOOKUP function returns the #N/A error!
So, it may seem quite hard to find out the exact reason behind the error showing. Well, it is because of an extra trailing space present after the word Candies in the Category column.
This problem may seem an easy-peasy issue, but it may lead to the worst suffering. As the spaces are invisible and hard to spot.
🔗 Get Solutions
You can manually check for the spaces whether they are present or not within the data. Or, you can use the TRIM function to remove all the trailing spaces.
Reason 5: Mistakes in the Lookup_value Argument of the VLOOKUP Syntax
Another issue that might look so silly but can lead to great suffering. If you have any syntax error regarding the VLOOKUP function or just a simple typo while addressing the lookup value, then this may lead to error showcasing.
For instance, in the following image, the lookup value is in the cell address, D14. But we’ve typed D144. This is just a simple type but causing #N/A error in the corresponding cell.
🔗 Get Solutions
Be careful about the function syntax or any kind of typos. Just by simply maintaining these etiquettes, you can avoid the #N/A error.
Things to Remember
📌 Make sure your lookup-value exists within the first column of your table array.
📌 Be careful about the syntax of the VLOOKUP function.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
Conclusion
To sum up, we have discussed 5 problems with their probable solutions behind the VLOOKUP function returns #N/A error even when a match result exists in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
Related Articles
- [Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value
- VLOOKUP Is Returning Just Formula Not Value in Excel
- Excel VLOOKUP Returning Column Header Instead of Value
- [Fixed!] Excel VLOOKUP Returning #N/A Error
- [Fixed!] Excel VLOOKUP Not Returning Correct Value
- [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel
