Why does the VLOOKUP function return #N/A.
There are 4 situations that explain why the error occurs.
- The value you are looking for does not exist in the table
This is the most common mistake. The value you are looking for does not exist in your table
- Incorrect selection of the lookup table
The column where to search must always be the first in the selection
- The references of your lookup table are not blocked
When you copy your formula for other cells, the references of your table have also changed
- Incorrect Data Type
If the data type is different between the value being searched and the search table, then the search cannot succeed
N/A stands for Not Applicable in English which we can translate as Value not found
Error 1: The value you are looking for does not exist in the reference table
The main reason why RECEHRCHEV returns #N/A is because the value sought simply does not exist in the reference table.
But in some situations, this result is interesting. It is using this technique that we can compare 2 columns and find the missing values.
Error 2: Incorrect selection of reference table
Many Excel users select all the columns in a table without really thinking about the impact. Here, we search for information based on the product name and the VLOOKUP function returns #N/A
The mistake here is that the lookup value (Cherry), is not in column A but in column B. With the VLOOKUP function, the first column of the selection must always contain the value you are looking for. In our example, we must therefore start our selection in column B. And now, VLOOKUP returns the price of the product
Error 3: Table reference is not blocked
This error is quite "tricky". It does not occur on the first formula but when the formula is copied. Such as, in the following example, at the time of copying, the table references have been changed because they do not have dollars to block the cells.
Explanations:
- The first formula is correct and returns the correct result for the D product
- When the formula is copied, all references of the lookup table are changed
- So, for the second formula, product A doesn't exist because it does not appear in the selection of the second argument of the function
So, remember to ALWAYS block references from your lookup table to avoid returning N/A with VLOOKUP.
Error 4: Different data types
Here the problem is the type of data between the value sought and the values in the reference table. The value 33 searched for is a number (framed to the right of the cell) while in the lookup table it's text (framed left).
The VLOOKUP formula is written correctly, but the difference in data type is the cause of N/A. In this case, you have to transform the data so that the data types match.
