 # Why VLOOKUP returns #N/A? #### Frédéric LE GUEN

Last Updated on 01/09/2023

Why does the VLOOKUP function return #N/A.
There are 4 situations that explain why the error occurs.

1. 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

2. Incorrect selection of the lookup table

The column where to search must always be the first in the selection

3. 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

4. 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:

1. The first formula is correct and returns the correct result for the D product
2. When the formula is copied, all references of the lookup table are changed
3. 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.

# Why VLOOKUP returns #N/A?

Last Updated on 01/09/2023

Why does the VLOOKUP function return #N/A.
There are 4 situations that explain why the error occurs.

1. 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

2. Incorrect selection of the lookup table

The column where to search must always be the first in the selection

3. 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

4. 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:

1. The first formula is correct and returns the correct result for the D product
2. When the formula is copied, all references of the lookup table are changed
3. 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.