Why VLOOKUP Returns #N/A When Match Exists (with Solutions)

We will be using a sample product price list as a dataset to demonstrate all the reasons.

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:

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.

Get Solutions

1. First Solution: Since the primary requirement about the lookup value is that it should exist within the first column of the table array, transfer the second column to the first column if possible.

2. Second Solution: Modify the table array a little bit. Currently, the table array is B5:E12. If we change the range to C5:E12, then column C will be the first column of the newly defined table array. The VLOOKUP function will then function properly. You have to update the column index, too. To return the value from the Price column, the new column index will be 3.

3. Third Solution: Use the INDEX and MATCH functions instead.

Reason 2 – Exact Match Isn’t Found

In the picture below, we’ve inserted the lookup value in cell D14, which is Cereal. Unfortunately, the only result in the column is Cereals, which isn’t an exact match.

Get Solutions

Write down the lookup value correctly within the insertion field. If you receive any #N/A error, recheck your dataset and correct your lookup value accordingly.

Reason 3 – The Lookup Value Is Smaller Than the Smallest Value in the Array

In the picture below the lookup value is 200, but the smallest value in the lookup range column is 207.

Get Solutions

Make sure that the lookup value is not smaller than the minimum value stored within the lookup range.

Reason 4 – Extra Spaces in Table Lookup Values

In this example, the formula is correct and the value seems to exist in the lookup range. However, the final value in the Category column contains a trailing space, which isn’t visible until you go into the Formula bar.

Get Solutions

Manually check for extra spaces in your dataset. You can use the TRIM function to remove all the trailing spaces.

Reason 5 – Mistakes in the Lookup_value Argument of the VLOOKUP Syntax

In the following image, the lookup value is in the cell address D14 but the formula refers to D144.

Get Solutions

Make sure you’ve input the correct cell references in the formula.

Things to Remember

• Make sure your lookup value exists within the first column of your table array.
• Check for spelling or extra spaces within your dataset.
• Be careful about the syntax of the VLOOKUP function.

Download the Practice Workbook

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

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel