FORMULA: Vlookup
(Part 2 of 3)
Rule 1 - The left column must contain the values being referenced.
Rule 2 - If you have duplicate values in the Reference Worksheet in
the leftmost column of the lookup range. If you do, the value
returned will be from the first row for that reference.
Rule 3 - Be careful copying and pasting formulas. You don’t want
your cell references to change when you drag and fill to populate
the other cells . After you define your range, you may need to
press F4 which will cycle through absolute and relative references.
You will likely want to select the option that includes a $ before
your Column and Row.
Rule 4 - Cell formats must be the same (between the
Lookup_value in the Primary Worksheet and the cells in column A
of the Reference Worksheet) (e.g. if the reference value is a date
field then the lookup field(s) must also be formatted as a date
field)
Problem What went wrong
Wrong value
returned
If range_lookup is TRUE or left out, the first column
needs to be sorted alphabetically or numerically. If
the first column isn't sorted, the return value might
be something you don't expect. Either sort the first
column, or use FALSE for an exact match.
#N/A in cell
•
If range_lookup is TRUE, then if the value in
the lookup_value is smaller than the smallest value
in the first column of the table_array, you'll get the
#N/A error value.
•
If range_lookup is FALSE, the #N/A error value
indicates that the exact number isn't found.
#REF! in cell If col_index_num is greater than the number of
columns in table-array, you'll get the #REF! error
value.
#VALUE! in cell If the table_array is less than 1, you'll get the
#VALUE! error value.
#NAME? in
cell
The #NAME? error value usually means that the
formula is missing quotes. To look up a person's
name, make sure you use quotes around the name
in the formula. For example, enter the name
as "Fontana" in
=VLOOKUP("Fontana",B2:E7,2,FALSE).
#SPILL! in cell This particular
#SPILL! error usually means that
your formula is relying on implicit intersection for
the lookup value, and using an entire column as a
reference. For example,
=VLOOKUP(A:A,A:C,2,FALSE). You can resolve the
issue by anchoring the lookup reference with the @
operator like this: =VLOOKUP(@A:A,A:C,2,FALSE).
Alternatively, you can use the traditional VLOOKUP
method and refer to a single cell instead of an
entire column: =VLOOKUP(A2,A:C,2,FALSE).
In MS Office 365 there is a new function called Xlookup
which is similar to Vlookup except there is no
[range_lookup] in the formula