Microsoft Excel

VLOOKUP troubleshooting tips

VLOOKUP isnt complicated, but its easy to accidently type the wrong thing or make other mistakes that lead to errors in your formula. On this page, youll find tips for
troubleshooting the VLOOKUP syntax. The following pages describe techniques for troubleshooting #N/A errors, which most frequently occur when youre trying to
find an exact match.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Unexpected Does this value exist in the left- Are you using a relative refer- Are you pointing to the Have you swapped the
most column of your lookup ence (e.g., A2:G145) when an correct column in the lookup arguments? Remember, use
results? table? If not, and its impractical absolute reference (e.g., table? To figure out what this FALSE for an exact matche.g.,
to move the column, you must $A$2:$G$145) is necessary? number should be, count over when youre looking up a proper
use another solution, such as This is important when youre to the right from the first col- name or specific product code.
INDEX and MATCH. Note that the copying the VLOOKUP formula umn in your lookup table. Use TRUE to find the closest
column's physical position in the to other cells. In this case, you Count that first column as 1. match to the lookup value if an
worksheet doesnt matter. If your typically want to lock the exact match doesnt existe.g.,
lookup table starts at column R lookup table to prevent mis- Be careful here. You wont when youre mapping test scores
and ends at column T, column R leading results. To quickly necessarily see an error if to a table of letter grades or in-
is the leftmost column. switch between reference youre off by a column, but come to a table of tax rates.
types, select the range you Excel may return the wrong
Does the format of the lookup If youre using TRUE, are the
entered for this argument, and datae.g., March sales num-
value match the format of the values in the first column of your
then press F4. Or, better yet, bers instead of April numbers.
matching value in the lookup lookup table sorted in ascending
use a defined name instead of
table? Errors often occur when Do you see the #REF! error? If (A to Z) order? If not, you may
a range; names use absolute
one of these values is not so, make sure the number you see unexpected results. (This
cell references by default.
formatted correctly. specify for this argument isnt sorting isnt required for FALSE.)
greater than the number of
If youre using text, did you re- Is your lookup table on a If youre using FALSE, do you see
columns in your lookup table.
member to put quotation marks different sheet or workbook? a #N/A error? If so, Excel cant
around that text? If youre typing If so, is it referenced correctly find a match. This may be be-
text directly (vs. using a cell refer- in this argument? Double- cause one doesnt exist. Or, a
ence such as A2), you must use check the sheet names, espe- match may exist, but Excel
quotation marks. cially if youre switching back doesnt interpret it as such
and forth between sheets as because of formatting problems
youre building the formula. or other issues.

2010 by Microsoft Corporation.

All rights reserved.
Microsoft Excel
VLOOKUP troubleshooting tips
Scenario 1: Excel returns a #N/A error in the cell. However, youre not sure why, because it looks like a match exists in the leftmost column of your lookup table.

Cause of #N/A error What to do about it Example

The number format of the lookup Verify that the number formats are identical. In the following example, the lookup table contains 2800911 (text), whereas the value
value may not match the format Although values may look like numbers, Excel in the lookup value is 2800911 (a number). To fix the problem, select the cell(s) that
of the matching value in the may be, in fact, storing these values as text. have the green triangles. When the error button appears, click it, and then click
lookup table. Numbers stored as text are left-aligned in- Convert to Number.
stead of right-aligned in the cell, and a little
green triangle usually appears in the upper-
left corner of the cell.

The first column or lookup value These spaces often occur when you bring data In the following lookup table, there are extra spaces before the page names in column
contains unnecessary leading or into Excel from databases or other external A. To remove these spaces, insert a temporary column next to column A, type =TRIM
trailing spaces, or extra spaces sources. (A2) in cell B2, and then press ENTER to remove the spaces. Then, copy the formula to
between words. You can remove the spaces manually or use the rest of the cells in column B.
the find and replace feature to do it. You can
also use the TRIM function, which removes
extra spaces from text, except for single spac-
es between words.

After the spaces are gone, paste the clean data from column B over the data in
column A, taking care to copy the values, not the underlying formula. Finally, delete
column B since it is no longer needed.

The first column or lookup value These characters sometimes show up when In the following example, SUBSTITUTE replaces the errant CHAR(160) character with
contains carriage returns, you copy or import data into Excel from the an empty space, CLEAN removes nonprinting characters, and TRIM removes extra
linefeeds, nonbreaking spaces, or web or other external sources. spaces, if they exist.
other special characters that are TRIM wont work here, at least not on its own. =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
embedded into the text. Instead, you can use CLEAN or SUBSTITUTE If you need more information about any of the functions mentioned here, open Excel,
or a combination of these functions to get press F1, and then search for the function name in Help.
rid of the characters.

2010 by Microsoft Corporation.

All rights reserved.
Microsoft Excel
VLOOKUP troubleshooting tips
Scenario 2: Excel returns a #N/A error in the cell because no match actually exists in the first column of the lookup table.

Cause of #N/A error What to do about it Example

The lookup value doesnt exist in If you like, you can replace the #N/A In Excel 2003 or later, you can use a combination of the IF, ISNA, and VLOOKUP functions
the leftmost column of the lookup error with a helpful message. to show a message instead of the #N/A error. For example, you can use a formula like so:
table.
You can also show an empty cell or a =IF(ISNA(VLOOKUP(A4, Pages!$A$2:$C$34,2, FALSE)), "Page doesn't exist", VLOOKUP
zero, so that you successfully sum a (A4, Pages!$A$2:$C$34,2, FALSE))
column of numbers. (#N/A errors will
prevent you from correctly using
VLOOKUP results in other formulas.)

Use similar formulas to show an empty cell, or to show a zero in the cell:

=IF(ISNA(VLOOKUP(A4, Pages!$A$2:$C$34,2, FALSE)), "", VLOOKUP(A4, Pages!

$A$2:$C$34,2, FALSE))

=IF(ISNA(VLOOKUP(A4, Pages!$A$2:$C$34,2, FALSE)), "0", VLOOKUP(A4, Pages!

$A$2:$C$34,2, FALSE))

In Excel 2007 or later, you can use the IFERROR function:

=IFERROR(VLOOKUP(A4, Pages!$A$2:$C$34,2, FALSE), "Page doesnt exist")

2010 by Microsoft Corporation.

All rights reserved.