How to troubleshoot and fix a VLOOKUP formulas, when the lookup numbers result in an #N/A error. Get the free workbook with the VLOOKUP problem and solutions.
To learn the basics of VLOOKUP, and see more examples, go to the VLOOKUP Function page
In this example, there is a simple lookup table, with category codes and category names. In the data entry table, a VLOOKUP formula should get the category name, based on a product's category code.
However, there is a problem -- the result in every row is an #N/A error. We'll troubleshoot the problem, and see different ways to fix it. You can download the Excel workbook, to see the problem and the solutions.
In this example, there is a lookup table, with numbered category codes in the first column, and category names in the second column.
The lookup table is named tblCat, and it is stored on the Lists sheet in the sample workbook. Back to Top
On the Problem sheet in the sample file, there is a table named tblProducts, with 3 columns -- Product, Code and Category.
The first column has an 8-digit product ID number that is manually entered in the list. The first 3 digits of each number show the category code.
For example, in the first product ID -- 12376543 -- the category code is 123.
In the second column, instead of having to type the category code, a formula extracts the first 3 digits from the Product ID in column A, to calculate the category code automatically: =LEFT(A2,3)
Finally, in the third column, a VLOOKUP formula will get the Category name from the lookup table (tblCat), based on the 3-digit code in the Code column:
Even though the VLOOKUP formula looks correct, it is returning an #N/A error in every row. All of the codes are in the Category lookup table, but the VLOOKUP formula can't find them correctly.
When you can see values that appear to match, a good way to start troubleshooting is to see if Excel sees the values as equal to one another. To do that in the sample file:
The result is FALSE. Excel does not see the values as equal, even though they look the same.
A common cause for this type of FALSE result is that one of the values is a number, and the other is text that looks like a number.
Note: In some cases, there is no apostrophe at the start of a text number.
Here is a quick way to check for real numbers:
The numbers on the Problem sheet only show a Count (3). The codes on the Lists sheet show Count of 3, and a Numerical Count of 3.
Here are a few other ways to see if a value is a real number:
To make the VLOOKUP formula work correctly, the values have to match. If the problem is caused by text numbers in one place, and real numbers in another, do the following to fix the problem:
The conversion can be done with a formula, or you can manually change the values, or use a macro to change them.
Shown below are options for:
In the sample file, the LEFT function is used in column B, to create the 3-digit category code. Because the LEFT function returns a text value, the 3-digit code is text -- not a real number.
To fix the LEFT formula, so it returns a real number, you can use one of the following formulas. Each formula converts the LEFT result to a real number, either with another function (VALUE, ABS, NUMBERVALUE), or with arithmetic (*1)
The following formulas all work equally well in column B, so use the formula that you prefer.
Another option is to leave the column B formula as is, and make a change in the VLOOKUP formula.
Similar to the options shown above, you can use another function, or some arithmetic, to convert the VLOOKUP formula's Lookup_value to a number.
The following formulas all work equally well in column C, so use the formula that you prefer.
One more formula option is to calculate the 3-digit code within the VLOOKUP formula, and convert it to a number there. You can refer to the column name in the table([@Product]) or to the cell address (A2).
For example, use one of the following formulas:
In the sample file, the Category Code is calculated with a formula. If you have imported data, or if you don't need the Code formulas any longer (no new products being added or edited), you can convert the formulas to values.
Then, use one of the methods shown here to fix the numbers: Convert Text to Numbers
In the screen shot below, the cells B2:B4 were copied, and pasted as values. Then the Text to Columns Wizard was used to convert the text to numbers.
NOTE: If you have to do this frequently, record a macro, so you can quickly convert the text to numbers.
To see the VLOOKUP problem and suggested solutions, download the sample workbook. The zipped file is in xlsx format, and does not contain any macros.
Last updated: February 13, 2017 10:23 AM