Tips vlookup excel

Microsoft Excel

VLOOKUP troubleshooting tips
VLOOKUP isn’t complicated, but it’s easy to accidently type the wrong thing or make other mistakes that lead to errors in you r formula. On this page, you’ll find tips for
troubleshooting the VLOOKUP syntax. The following pages describe techniques for troubleshooting #N/A errors, which most frequ ently occur when you’re trying to
find an exactmatch.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Unexpected
results?

 Does this value exist in the leftmost column of your lookup
table? If not, and it’s impractical
to move the column, you must
use another solution, such as
INDEX and MATCH. Note that the
column's physical position in the
worksheet doesn’t matter. If your
lookup table starts at column R
andends at column T, column R
is the leftmost column.

 Does the format of the lookup
value match the format of the
matching value in the lookup
table? Errors often occur when
one of these values is not
formatted correctly.

 If you’re using text, did you remember to put quotation marks
around that text? If you’re typing
text directly (vs. using a cell reference such as A2), you must usequotation marks.

© 2010 by Microsoft Corporation.
All rights reserved.

 Are you using a relative refer-  Are you pointing to the
ence (e.g., A2:G145) when an
absolute reference (e.g.,
$A$2:$G$145) is necessary?
This is important when you’re
copying the VLOOKUP formula
to other cells. In this case, you
typically want to “lock” the
lookup table to prevent misleading results. Toquickly
switch between reference
types, select the range you
entered for this argument, and
then press F4. Or, better yet,
use a defined name instead of

a range; names use absolute
cell references by default.
Is your lookup table on a
different sheet or workbook?
If so, is it referenced correctly
in this argument? Doublecheck the sheet names, especially if you’re switching back
andforth between sheets as
you’re building the formula.

 Have you swapped the

correct column in the lookup
table? To figure out what this
number should be, count over
to the right from the first column in your lookup table.
Count that first column as 1.
Be careful here. You won’t
necessarily see an error if
you’re off by a column, but
Excel may return the wrong
data—e.g., March salesnumbers instead of April numbers.

arguments? Remember, use
FALSE for an exact match—e.g.,
when you’re looking up a proper
name or specific product code.
Use TRUE to find the closest
match to the lookup value if an
exact match doesn’t exist—e.g.,
when you’re mapping test scores
to a table of letter grades or income to a table of tax rates.

 If you’re using TRUE, are the

Do you seethe #REF! error? If
so, make sure the number you
specify for this argument isn’t
greater than the number of

columns in your lookup table.

values in the first column of your
lookup table sorted in ascending
(A to Z) order? If not, you may
see unexpected results. (This
sorting isn’t required for FALSE.)
If you’re using FALSE, do you see
a #N/A error? If so, Excel can’t
find a match.This may be because one doesn’t exist. Or, a
match may exist, but Excel
doesn’t interpret it as such
because of formatting problems
or other issues.

Microsoft Excel

VLOOKUP troubleshooting tips
Scenario 1: Excel returns a #N/A error in the cell. However, you’re not sure why, because it looks like a match exists in the leftmost column of your lookup table.
Cause of #N/A error

Whatto 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
in the lookup value is 2800911 (a number). To fix the problem, select the cell(s) that
have the green triangles. When the error button appears, click it, and then click
Convert to Number....