Why VLOOKUP returns #N/A?

Why VLOOKUP returns #N/A?
Last Updated on 27/02/2024
Reading time: 3 minutes

Why does the VLOOKUP function return #N/A?
4 situations 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.

The lookup product doesnt exist in the 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.

How to compare 2 columns with Excel

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

VLOOKUP returns NA because the selection of the table starts in Column 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

The first column of the selection has the lookup value

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.

The references of the tables have changed this is why VLOOKUP returns NA

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
Block the reference of the lookup table with dollars

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

Different type of Data returns NA with VLOOKUP

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.

Same Data Type return the correct result

Tutorial Video

Leave a Reply

Your email address will not be published. Required fields are marked *

Why VLOOKUP returns #N/A?

Reading time: 3 minutes
Last Updated on 27/02/2024

Why does the VLOOKUP function return #N/A?
4 situations 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.

The lookup product doesnt exist in the 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.

How to compare 2 columns with Excel

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

VLOOKUP returns NA because the selection of the table starts in Column 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

The first column of the selection has the lookup value

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.

The references of the tables have changed this is why VLOOKUP returns NA

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
Block the reference of the lookup table with dollars

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

Different type of Data returns NA with VLOOKUP

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.

Same Data Type return the correct result

Tutorial Video

Leave a Reply

Your email address will not be published. Required fields are marked *