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.
There are eight possible errors when using the VLOOKUP function. Read the following article for a more detailed explanation of the other VLOOKUP errors
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.
Some people want this #N/A error not to occur, because it affects to the other formula. #N/A error appears in total bill (cell H10) due to #N/A error in cell H8.
You can use the solution below to avoid #N/A error from appearing because of nonexisting lookup_value in the first column of table_array.
The other alternatives, you can use the AGGREGATE function or array formula. The following article discusses how to prevent divide by zero errors. You can use the same solution to avoid #N/A error in the total bill.
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.
Use excel TRIM function to remove spaces either in front or behind quickly
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.
Another way to convert text to numbers is to use the excel NUMBERVALUE function, whereas to convert numbers to text using the excel TEXT function.
