VLOOKUP NA Error – Causes and Solutions

VLOOKUP NA error is the most common error when using the VLOOKUP function. This error occurs because the lookup_value is not in the first column of table_array for several reasons.

VLOOKUP NA error because of the wrong table_array range

There are two possibilities for the wrong table_array range. First, the table_array range shifts because of the process of copying a formula. This error has been explained in detail with the solution in “The Wrong Cell Reference” article. Second, the table_array range point to the wrong location. If this happens, you must fix the formula and point the table_array range to the correct place.

VLOOKUP NA error because the data is missing from the table_array

This error occurs because something is missing in table_array because it was deleted. The picture above explains the formula in cell H5 is correct, the same as the formula in the other cell in the same column, but the word “Caffe Latte” disappears from the price table, this is what causes #N/A error, even though there is a price in the right column. The VLOOKUP function cannot retrieve the price value if there is no data in the first column of table_array.

VLOOKUP NA error because the lookup_value looks for nonexisting value

This error occurs because lookup_value is not in the first column of table_array. The image below explains no “Hot Brewed Coffee” in the table prices, this is what causes #N/A error, although the formula is correct.

VLOOKUP NA error because there is a blank space

Blank space is hard to detect error. Blank space could be in the lookup_value or in the first column of table_array. If a blank space in the front, it is easier to find out, but if a blank space in the back it will be not visible Look at the image above; there are two #N/A errors in cell H5 and cell H7. #N/A error in cell H7 because of a blank space in front of the word “Salted Caramel Mocha”. Pay attention to cell F7, the word “Salted Caramel Mocha” is a bit indented, that’s where the blank space is. #N/A error in cell H5 because there is a blank space behind the word “Caffe Latte”. A cell containing blank space is cell A2, but the appearance is not visible, it must be checked by editing the cell, of course, this will take time if there are many data.

VLOOKUP NA error because numbers are stored as text

The first column of table_array and the lookup_value must have the same data type. If it is text, then all must be text, if it is number, then all must be number. The problem arises because numbers can be stored as text by adding single quotes in front of the number. An appearance is a number, but Excel acknowledges it as a text. Look at the image above. The VLOOKUP function looks for the price based on the item code. As a result, all formulas return #N/A errors. Look again, the lookup_value is a number (column G), and the first column of table_array (column A) is a number too. Then why the VLOOKUP function returns a #N/A error? Look more closely. All item code in column A (table_array) is right aligned, while all item code in column G (lookup_value) is left aligned. Numbers by default is right-aligned, and text by default is left aligned, this is the problem. lookup value is text while the first column of table_array is a number. Another way to detect numbers stored as text is by the appearance of small green triangles on the top left of each cell. Excel will notify if there are numbers stored as text, whether to convert to numbers or to be ignored.

The Solution

The solution for removing #N/A error is to convert all item code in the first column of table_array to text or to convert all item code in the lookup_value to the number. For example, the chosen solution is to convert all item code in the lookup_value to numbers. See the image below for how to do it. You got the prices after conversion completed.

Related Function

Related Article