Fix VLOOKUP Numbers Problem

How to troubleshoot and fix a VLOOKUP formula, when the lookup numbers result in an #N/A error. Get the free workbook with the VLOOKUP problem and solutions.

VLOOKUP Number Problem

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 spreadsheet, and the solutions.

Tip: To learn the basics of VLOOKUP Excel function, and see more examples, go to the VLOOKUP Function page

Video: VLOOKUP Number Problem

To see the steps for fixing the Excel VLOOKUP function problem when the lookup table has text values, and the lookup value is a number, watch this short video. There are written steps below the video

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

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. In each row, VLOOKUP returns and error value in column C.

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)

How the VLOOKUP Formula Works

The VLOOKUP function has the followinsg syntax for its arguments:

• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In this example, the formula will:

• lookup_value - look for the value in the Code column
• table_array - look in the table named tblCat
• col_index_num - return a value from the 2nd column in tblCat
• range_lookup - find an exact match for the lookup value

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 be an exact value match, a good way to start troubleshooting is to see if Excel sees the values as an exact match to one another.

To do that in the sample file, follow these steps:

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.

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

In the status bar:

• 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.

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

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))

Formula - Change VLOOKUP

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

• The first set of options below convert the code to a number
• The second set of options calculate and convert the code

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)

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)

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.

Get the Sample File

To see the VLOOKUP problem and suggested solutions, get the sample workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

VLOOKUP

VLOOKUP from Another Workbook

HLOOKUP

Lookup - 2 Criteria

INDEX / MATCH

Functions List