Being able to use the VLOOKUP function
separates the power Excel user from the
casual user. Microsoft estimates that
90% of those in the Excel universe have
never used the VLOOKUP function. As
accountants, however, we’d be lost with-
out it. Whether it’s used to look up a
commission rate or find the name for a
cost center, VLOOKUP is ubiquitous in
accounting worksheets. This month,
we’ll take a look at 12 variations on the
standard VLOOKUP formula that will
help you become even better at using
this valuable function.
Table 1 shows the 12 variations. The
formulas assume that you are looking up
a value in cell A2. The lookup table is in
cells X1:Z100, and row 1 contains
headings.
Formula 1 shows the basic VLOOKUP
formula that will return the first exact
match. Imagine you’re looking for the
value from cell A2 in the left-most col-
umn of the lookup table (column X). The
second argument specifies the table
array, X2:Z100. Use dollar signs if you
plan on copying the formula into other
cells. This keeps the array fixed. When
the value is found, you want the formula
to return the second column from the
table, so specify 2 as the third argument.
The fourth argument of False specifies
that you need an exact match.
Few people know that you can also
do a wild-card search. The syntax in For-
mula 2 will find the first item in the table
that contains an x.
Note that VLOOKUP isn’t case sensi-
tive. ABC will match ABc. If you need to
find an exact match that is case sensi-
tive, combine INDEX, MATCH, and
EXACT, as shown in Formula 3. After
typing this formula, you must hold down
Ctrl+Shift+Enter to tell Excel to evaluate
the formula as an array formula.
Some lookups won’t have every possi-
ble value in the table. You might specify
that sales between $0 and $9,999 get a
2% commission, sales between $10,000
and $49,999 get a 2.5% commission,
and sales of $50,000 or more get a 3%
commission. This table would need only
three rows, with 0, 10000, and 50000 in
the first column and the commission per-
centages in the second. In Formula 4,
True is used in the fourth argument
(instead of False). If Excel can’t find the
exact value in the table, using True tells it
to return the row that is just smaller
than the value you are looking up. This is
called an “approximate match.” When
using this formula, make sure that the
lookup table is sorted ascending. If you
want to find the row that’s just greater
than the value you’re looking up, such
as when calculating a finance charge for
late payments where you pay 1% for
each portion of a month that the pay-
ment is late, sort the table descending
and use the syntax in Formula 5.
The approximate match is also a good
trick for finding the final entry in a row
or column. To find the last entry in a col-
umn, do a VLOOKUP that looks for a
number larger than the expected values
in the column. As Formula 6 shows, if
you search for 999999999 and Excel
can’t find anything larger, then the func-
tion will return the last numeric entry in
the column. To get the last text entry in
a column, search for ZZZZZ instead, as
shown in Formula 7.
MATCH and VLOOKUP are similar
functions. Both offer the exact match or
approximate match feature. While
VLOOKUP uses False to specify an exact
match, MATCH uses zero. While
VLOOKUP uses True for an approximate
match, MATCH uses 1. The main differ-
ence is that VLOOKUP returns a value
TECHNOLOGY
EXCEL
VLOOKUP Variations
By Bill Jelen
60 STRATEGIC FINANCE
I
May 2012
