Contextures

VLOOKUP Numbers Problem

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.

VLOOKUP Number Problem

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.

vlookup errors

The Lookup Table

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

category lookup table

Product Table and VLOOKUP

On the Problem sheet in the sample file, there is a table named tblProducts, with 3 columns -- Product, Code and Category.

product table with formulas

Product

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.

Code

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)

Category

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:

=VLOOKUP([@Code],tblCat,2,0)

Troubleshoot VLOOKUP Errors

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:

  1. On the Problem sheet, click in cell E2 and type and equal sign, to start a formula
  2. Click on the 123 code in cell B2
  3. Type an equal sign
  4. Go to the Lists sheet, and click on the 123 code in cell B2
  5. Press Enter to see the result.

The result is FALSE. Excel does not see the values as equal, even though they look the same.

category codes are not equal

Text or Number

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.

  • The lookup table may contain '123 (text), and the value to look up is 123 (a number).
  • OR, the lookup table may contain 123 (a number), and the value to look up is '123 (text)

Note: In some cases, there is no apostrophe at the start of a text number.

Check for Numbers With a Count

Here is a quick way to check for real numbers:

  1. Select two or more cells that contain the numbers you want to check
  2. Look in the status bar, at the bottom of the Excel window.
    • If only a Count is showing, the values are test, not numbers.
    • If Numerical Count is also showing, and is equal to the Count, all the values are 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.

Count and Numerical Count

Other Ways to Check for Numbers

Here are a few other ways to see if a value is a real number:

  • Use the ISNUMBER function. For example: =ISNUMBER(B2)
  • Check the alignment -- by default, text will left align, and numbers right align.
  • Select the VLOOKUP formula cell, and click the fx button in the Formula Bar. In the Function Arguments window, check the Lookup_value and Table_array values
    • text values are wrapped with quote marks
    • real number have NO quote marks

text values with quote marks

Fix the Problem

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:

  • convert the real number to text, so both values are text
  • or, convert the text value to a number, so both values are numbers

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:

  • Changing the Code formula in column B
  • Changing the VLOOKUP formula in column C
  • Changing the values (removes the formulas in some cases)

Formula - Change Code to Number

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.

  • =VALUE(LEFT(A2,3))
  • =LEFT(A2,3)*1
  • =ABS(LEFT(A2,3))
  • =NUMBERVALUE(LEFT(A2,3))

convert code to number

Formula - Change VLOOKUP

Another option is to leave the column B formula as is, and make a change in the VLOOKUP formula.

Formula - Change Lookup to Number

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.

  • =VLOOKUP([@Code]*1,tblCat,2,0)
  • =VLOOKUP([@Code]+0,tblCat,2,0)
  • =VLOOKUP(--[@Code],tblCat,2,0)
  • =VLOOKUP(VALUE([@Code]),tblCat,2,0)
  • =VLOOKUP(NUMBERVALUE([@Code]),tblCat,2,0)
  • =VLOOKUP(ABS([@Code]),tblCat,2,0)

convert to number in VLOOKUP

Formula - Get and Convert Code in VLOOKUP

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:

  • =VLOOKUP(LEFT([@Product],3)*1,tblCat,2,0)
  • =VLOOKUP(VALUE(LEFT(A2,3)),tblCat,2,0)
  • =VLOOKUP(VALUE(LEFT([@Product],3)),tblCat,2,0)

convert to number in VLOOKUP

Change the Data

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.

convert to number in VLOOKUP

Download the Sample File

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.

More Tutorials

VLOOKUP

HLOOKUP

INDEX / MATCH

Functions List

 

30 Excel Functions in 30 Days

 

 

 

Excel Tools

 

 

Pivot Power Premium

 

 

 

30 Excel Functions in 30 Days

 

 

 

Excel Tools

 

 

30 Excel Functions in 30 Days

 

Last updated: February 13, 2017 10:23 AM