Why does the VLOOKUP function return #N/A error? This error happens often and can confuse Excel users.

N/A stands for Not Applicable in English, which we can translate as Value not found. VLOOKUP errors occur for different reasons. Understanding them will help you fix issues quickly. Here are the most common causes:

• The value you want to find is not in the table.
• You selected the wrong lookup table.
• Your lookup table references are not locked.
• The data types do not match.

By learning these causes, you can solve VLOOKUP errors faster and improve your Excel skills.

Error 1: The value you are looking for doesn't exist in the reference table

This is the main reason why VLOOKUP returns #N/A error. Spain is not in the table

Explanations:

• The VLOOKUP function is well written, but the lookup value is not in the reference table.
• But "Not found" could be a good answer in some situations. For instance, we have used this result in the solution to compare two columns.

Error 2: Incorrect selection of reference table

Many Excel users select all table columns without considering the impact.

Here, we search the price according to the product name, and VLOOKUP returns #N/A 😟🤔

Explanations:

1. The mistake here is the selection of second argument
2. The first column of the second argument (the reference table) MUST contain the value you are looking for
3. So, in this situation, we must start the selection from column B.

Error 3: Table reference is not blocked

This error is tricky. It does not occur with the first formula but when you copy it for other cells.

Explanations:

1. When copied, the references in the lookup table change 😮
2. So, for the second formula, country USA is missing in the range of cells A3:C6. In other words, the VLOOKUP function doesn't "see" the country USA
3. The solution is to lock table's reference with the shortcut F4 to stay focus on your data table.

Error 4: Different data types

In this last situation, the problem is Data Type

• The lookup value is '33'
• But in the first screenshot, the result returns #N/A 😡

Explanations:

• The formula is the same for each screenshots 😮
• But, on the first screenshot, the values in the reference table are on the left of the cell; so it's TEXT
• But in the second screenshot, the data type in the reference table are on the right of the cell; so it's NUMBER
• Because the lookup value and searching value have the same data type, VLOOKUP returns a result 👍

Tutorial Video