Home > Formulas > Lookup > VLOOKUP Fix VLOOKUP Numbers Problem Here are a few ways you can troubleshoot and fix a VLOOKUP problem, when the formula result in an #N/A error. I show the steps in a short video, and there are written steps too, and a file to download.

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

Product 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 - VLOOKUP

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

• Product: 8-digit product ID number, manually entered
• Code: LEFT formula returns first 3 numbers of product code
• Category: VLOOKUP formula gets category name from lookup table

The screen shot below shows the problem. In each row, the VLOOKUP formula in column C returns an error value, instead of the correct category name.

Code Formula - LEFT

In column A, the first 3 characters in the Product ID represent the 3-digit category code number.

In column B, you don't have to type the category code. The following formula returns the first 3 digits from the Product ID automatically:

• =LEFT(A2,3)

Category Formula - VLOOKUP

Finally, in column C, the following VLOOKUP formula returns 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 following 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, not an approximate match

A) Troubleshoot VLOOKUP Errors

Even though the VLOOKUP formula looks correct, it returns an #N/A error in every row. To figure out how to get rid of those errors, I'll do a few troubleshooting steps, described in the sections below.

-- A1) Visual Check - Look at a few of the numbers, in both tables, to see if they look correct. Maybe there are typos!

-- A2) Formula Check - Use a simple formula to compare two values. Does Excel see them as equal?

-- A3) Number Test - Use the Status Bar for a quick check for real numbers versus text numbers. This is the problem in many cases!

A1. Visual Check

To begin troubleshooting, I temporarily put the tables side by side on the worksheet, to do a visual check. Maybe there are typos that can be fixed quickly.

• All 3 codes are in the Category lookup table
• VLOOKUP formula can't find the matching codes correctly.

A2. Formula Check for Exact Match

In my visual check, it looked like the correct code numbers were in both tables. However, Excel might "see" things the I don't!

So, for my next troubleshooting step, I'll use a formula to check if Microsoft Excel sees the code values as an exact match to one another.

To do this Exact Match test in the sample file, follow these steps:

1. On the Problem sheet, click in cell E2, to select it
2. Type an equal sign, to start a formula
3. Next, click on the 123 code in cell B2
4. Type an equal sign
5. Then, go to the Lists sheet, and click on cell B2, which contains the 123 code
6. Press Enter to see the formula result.

In the screen shot below, the formula result is FALSE. That means Excel does not see the values as equal, even though they look the same.

A3. Check for Text vs Real Numbers

A common cause for VLOOKUP number errors is that:

• one of the values is a real number
• the other is text that looks like a number

For example,

• Lookup table contains '123 (text)
• Value to look up is 123 (real number).

Note: In some cases, there is no apostrophe at the start of a text number. That can make it even trickier to detect those "text" numbers!

A3a) Status Bar Test

To quickly check if a list contains real numbers, you can use an easy Status Bar check.

Follow these steps to check for real numbers:

1. First, select two or more cells that contain the numbers you want to check
2. Next, look in the status bar, at the bottom of the Excel window, to see what calculations are showing.

Real Numbers

In the screen shot below, the Status bar shows Average, Count, and Numerical Count, for the code numbers on the Lists sheet.

• Count - counts any type of data, including text or numbers
• Numerical Count - counts numbers only -- the three selected cells contain real numbers

Text Numbers

In the next screen shot, the Status bar shows only a Count for the code numbers on the Problem sheet.

There is no Numerical Count, so that shows the three selected cells contain text numbers, not real numbers.

A3b) More Ways to Check for Numbers

Here are 3 more ways to see if a value is a real number:

1. Formula: Use the ISNUMBER function. For example: =ISNUMBER(B2)
2. Alignment: Check the cell alignment -- by default, text will left align, and numbers right align.
3. fx Button: 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

B) Fix the VLOOKUP Problem

To make the VLOOKUP formula work correctly, the values to look up, have to match one of the values in the lookup table.

If the VLOOKUP problem is caused by text numbers in one place, and real numbers in another, there are two ways 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

In the following sections, there are examples for converting the numbers.

3 Ways to Fix VLOOKUP Number Problem

To convert the values, you can use a formula, or you can manually change the values, or you can use a macro to change them.

The examples below show how to:

--B1) Change text lookup value to real number

--B2) Change text to number in VLOOKUP formula

--B3) Change values (removes the formulas in some cases)

B1) Change Text Lookup 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))

B2) Change Text in VLOOKUP

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

-- B2a) First set of formulas below convert lookup value to a number

-- B2b) Second set of formulas calculate and convert lookup value

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

These formula examples all work equally well in column C, so use the formula that you like best, or create a different formula!

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

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

B3) Change 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 a quick Excel trick, to change text number values to real numbers.

There are detailed steps, and a video, on the Fix Excel Numbers page. If you have to do this frequently, there's also a macro, to do the job quickly.

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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

VLOOKUP

VLOOKUP from Another Workbook

HLOOKUP

Lookup - 2 Criteria

INDEX / MATCH

Functions List