Excel
Lookup Functions
Excel’s VLOOKUP vs INDEXMATCH Functions
Most Excel users need to look up data in workbooks. But what's
the best lookup method?
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Excel offers two primary lookup methods:
VLOOKUP and
INDEXMATCH. Although the two methods are similar, INDEXMATCH
is more powerful.
(You
can download the workbook with the following examples here.)
I suspect, however, that VLOOKUP is betterknown and more widely
used.
Probably, this is because if you want to look up something it
makes sense to use a function with "lookup" in its name.
In this article I'll explain both VLOOKUP and INDEXMATCH. And along the way, I’ll explain the advantages and
disadvantages of these two methods.
There are, of course, two additional lookup functions:
HLOOKUP works just like VLOOKUP, but horizontally rather than
vertically. So everything I say about VLOOKUP also applies to HLOOKUP.
LOOKUP was designed for compatibility with Lotus 123; and
123’s @LOOKUP function was designed for compatibility with
VisiCalc. So LOOKUP is less powerful than VLOOKUP. However, it
does have one advantage over VLOOKUP, which I’ll in discuss in a
later article in this series.
The VLOOKUP function has these arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
The range_lookup argument is optional, often overlooked, but
very useful. This is because range_lookup determines whether
VLOOKUP returns an approximate match or an exact match.
Although Excel actually uses a different technique, here’s a
simple way to think about how approximate matches work:
Excel works its way down the first column until it finds a value
greater than the lookup value. Then it backs up one row to
return its results. If the lookup value is greater than the last
number in the column, it delivers that last result.
When Should You Use an Approximate Match?
The only time you should use an approximate match is when you
DEFINITELY need an approximate match. If you need an exact
match, and if you want accurate results, NEVER use an
approximate match.
By default, VLOOKUP returns an approximate match. This is
unfortunate, for two reasons. First, in my experience, most
Excel users need exact matches most of the time. Second,
approximate matches are risky, because they can return false
results.
This figure illustrates a typical occasion when approximate
matches are needed.
The figure shows a schedule of quantity discounts. If a purchase
is between 1 and 4 units, no discount is offered. A purchase
from 5 through 24 units gets a 5% discount, and so on. And any
purchase of 200 or more units gets a 20% discount.
The approximatematch feature allows us to look up the discount
for an order of any size. Cell E4, for example, shows that the
discount for a purchase of 7 units is 5%.
Here’s the formula for this calculation:
E4: =VLOOKUP(E$3,$A$3:$B$7,2,TRUE)
(The last argument is the optional range_lookup value. It
could have been
omitted, because the default value for this argument is TRUE.)
The formula in cell E7
in the figure above is similar. It shows that VLOOKUP works
as you expect it to for large values. That is, any purchase in
excess of the largest quantity will get the largest quantity
discount.
Notice in the figure that the units are sorted in ascending
order. This is an ABSOLUTE requirement. If your data isn’t
sorted, you can get false results.
How to Get False Results from Excel Lookup Functions
Your data MUST be sorted when you use approximate matches.
This is true for all Excel lookup functions that return
approximate matches.
The reason for this requirement is that Excel doesn’t just work
its way down a column or across a row for approximate matches.
Instead, Excel uses a binary search technique. That is, Excel
checks the middle of the list. If that value is less than the
lookup value, it jumps to the middle of the following section;
if the value is less, it jumps to the middle of the preceding
section. It continues to halve the distance until it finds the
correct result.
This technique is much faster than searching down a row or
column, but it can produce unexpected results if the data isn’t
sorted.
Often, if you do an approximate match against unsorted data, an
Excel lookup function will return an error value. That’s the
best result because it alerts you to a problem.
But unfortunately, when Excel performs an approximate match
against unsorted data, it can produce false results. For
example, this figure illustrates two types of incorrect results.
The SKU #1 example searches for a value that’s not in the table.
The two formulas in the example are:
F3: =VLOOKUP(F$2,$A$4:$C$8,2,TRUE)
F4: =VLOOKUP(F$2,$A$4:$C$8,3,TRUE)
The VLOOKUP function has these arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Because the range_lookup value is TRUE in cells F3 and F4, these
formulas are looking for approximate matches.
As you can see in the figure, the two formulas return results
for the SKU code A101 instead. This is a significant problem, because
Excel returns a false result, and that’s dangerous.
The SKU #2 example uses similar formulas to search for a value
that IS in the table. But because the SKUs column isn't sorted, it also returns values for
the SKU code A101
instead of the correct result.
So the two main conclusions are obvious:
1. Use approximate matches only when you have a good reason
to do so.
2. Be CERTAIN your data is sorted when you use approximate
matches.
How to Use Exact Matches with Excel’s VLOOKUP and HLOOKUP Functions
The default behavior of VLOOKUP and HLOOKUP is to return an
approximate match.
To understand why Microsoft probably made this choice, consider
that Excel’s four lookup functions (LOOKUP,
VLOOKUP,
HLOOKUP,
and MATCH) were written at least 20 years ago. Back then,
computers were much slower than they are today. Back then, using
an exactmatch search caused calculation to slow noticeably.
Therefore, the Excel team set up the fastest method – the
approximate match method – as the default search method.
But today, for most purposes, there’s no apparent difference in
calculation time between an exact and an approximate match.
Today, therefore, Excel’s default search method is unfortunate,
for at least two reasons. First, Excel users typically need
exact matches more frequently than they need approximate
matches. Second, approximate matches can
produce false results.
Let’s see how exact matches work with typical data…
The formulas in cells F2 and F3 return correct results. And
because the formulas in cells F6 and F7 look up data that doesn't
exist, they also return correct results.
Columns AC show a simple database of SKUs (Stock Keeping Units)
with their descriptions and prices.
Column F shows two sets of examples.
Notice that the SKU data in column A isn’t sorted. When you use
exact matches, the sort order doesn’t matter.
The SKU #1 example searches for information about
the SKU code B19, using
these formulas:
F2: =VLOOKUP(F$1,$A$3:$C$7,2,FALSE)
F3: =VLOOKUP(F$1,$A$3:$C$7,3,FALSE)
Remember that the VLOOKUP function has these arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Because the range_lookup value is FALSE in these formulas, they
are looking for exact matches. And because the SKU code B19 exists in the
table, the formulas find and return the correct results.
That’s GREAT news…We got a CORRECT result!
The SKU #2 example offers even better news. Here are its
formulas:
F6: =VLOOKUP(F$5,$A$3:$C$7,2,FALSE)
F7: =VLOOKUP(F$5,$A$3:$C$7,3,FALSE)
Here, we get error values when we search for data that’s not in
the list. That’s even better news, because we’re not getting
false data. That is, when we use only exact matches we can rely
on getting an error value if we search for data that doesn’t
exist.
The Limitations of VLOOKUP and HLOOKUP
The VLOOKUP formula uses this syntax:
=VLOOKUP(lookup_value, table_array, col_index_num,
range_lookup)
Let’s use this function to look up some values from this little
database.
To look up “G23″ in
the SKUs column and return its description, we would
use:
=VLOOKUP("G23",$A$3:$C$7,2,FALSE)
To look up “G23″ in SKUs and return its price, we would use:
=VLOOKUP("G23",$A$3:$C$7,3,FALSE)
To look up “Coats” in
the Items column and return its price, we would use:
=VLOOKUP("Coats",$B$3:$C$7,2,FALSE)
That’s
fine. But how can we use VLOOKUP to look up “Shoes” in the Items
column and return its SKU?
We can’t.
The reason is simple. VLOOKUP only can look up values in the
first column of the table_array, and we only can return values
from within the table array. So if we want to look up a value in
one column and return values to the left of that column, we’re
out of luck.
(No, it wouldn’t work if we try to use a negative value for
the col_index_num.)
To return the value we need, you would think that we could use
the LOOKUP function, which has this format:
=LOOKUP(lookup_value, lookup_vector, result_vector)
The problem is that LOOKUP’s lookup_vector column MUST be sorted
in ascending order. So if our database were sorted by the Item
column we could return its SKU, which is something that VLOOKUP
can’t do.
But because the data isn’t sorted, we’re out of luck…at least
for LOOKUP, VLOOKUP, and HLOOKUP.
Unfortunately, this isn’t the only limitation of these three
functions. The limitations are why I haven’t used these three
functions since about 1990. Instead, I only use INDEXMATCH.
Excel’s Best Lookup Method: INDEXMATCH
The most powerful and flexible way to look up data in Excel
is the INDEXMATCH method. It relies on two Excel functions:
=INDEX(reference, row_num, column_num)
 reference—a range of cells
 row_num—the row in reference from which to return data.
 column_num—the column in reference from which to return data.
If reference is one row or column, the
INDEX function can use this
syntax:=INDEX(reference, cell_num)
=MATCH(lookup_value, lookup_array, match_type)
 lookup_value—The value to match in lookup_array.
 lookup_array—A range of cells with data.
 match_type—Specifies how Excel matches the lookup_value with
values in the lookup_array. For exact matches, always use 0 for
this argument.
The figure below shows the INDEXMATCH method in action…
B11: =MATCH($A11,$A$3:$A$7,0)
This formula tells us that the text “G23” is found in the third
row of the SKUs column. And the formula copied to B12 says that
“A101” is found in the second row.
Notice that because we want an exact match, the last argument in
cell B11 has a value of zero.
C11: =INDEX($B$3:$B$7,$B11)
This formula for cell C11 returns the item for the cell number specified by
the value in cell B11.
D11: =INDEX($C$3:$C$7,$B11)
And this formula for cell D11 returns the price for the cell number specified
in cell B11.
Of course, we could have combined both the INDEX and MATCH into
one formula, as shown in cell E11:
E11: =INDEX($C$3:$C$7,
MATCH($A11,$A$3:$A$7,0))
This combination works just like the VLOOKUP function in cell
F11:
F11: =VLOOKUP($A11,$A$3:$C$7,3,FALSE)
So far, INDEXMATCH and VLOOKUP have the same capabilities. But
now consider the formulas in cells B16 and C16:
B16: =MATCH($A16,$B$3:$B$7,0)
C16: =INDEX($A$3:$A$7,$B16)
In these two formulas, we look up “Ties” in the column of Items and return its
SKU from the column of SKUs. This is a result that VLOOKUP can’t
produce, as I explained above.
More commonly, we combine these formulas into one:
E16:
=INDEX($C$3:$C$7,MATCH($A16,$B$3:$B$7,0))
The INDEXMATCH method has many additional advantages that
you’ll discover as you use this method.
If you aren’t using INDEXMATCH for your lookups, you’re missing
a lot of power. Give it a try.
Take Your Next Steps
You can take the next step with INDEXMATCH in two ways.
First, you can
download the completed workbook
here.
Second, if you want to add advanced lookups to your normal
reports and analyses, I can help you in three ways. To learn more,
see
Excel Training, Coaching, and Consulting.
Tags: #excel, #vlookup, #index, #match,#indexmatch, #lookup
