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:

=VLOOKUP($D$14,B5:E12,4,0)

within cell D15.

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.

=INDEX(E5:E12,MATCH(D14,C5:C12,0))

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.