Excel Lookup Functions
Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
When you want to look up data in Excel, you should never use a
worksheet function with "lookup" in its name. Here's why...
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
(Download the workbook.)
function is more popular than the
function, probably because when Excel users need to look up data
then a "lookup" function would be a reasonable choice to make.
This is too bad, because …
1. INDEX-MATCH is much more flexible than Excel's "lookup"
2. At its worst, INDEX-MATCH is slightly faster than VLOOKUP; at
its best, INDEX-MATCH is many-times faster.
I can think of only two reasons you ever should use VLOOKUP (or
HLOOKUP, which does the same thing, but sideways). First, you
might not know how to use INDEX-MATCH. If that’s the problem, I
hope this post will help.
Second, you might be working with Excel beginners who sort of
understand VLOOKUP but who know nothing about INDEX-MATCH. If
that’s the problem, now is the time to point them in the right
direction. Just send them a link to this post!
A Quick Introduction to VLOOKUP and INDEX-MATCH
The following figure shows a small database in columns A-C and
several uses of both VLOOKUP and INDEX-MATCH in the remaining
columns. To make formulas easier to talk about, I’ve assigned
the following range names:
And here’s Sheet1:
download this workbook here.)
Here are the three functions we’re working with:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
=INDEX(range, row_index_num, column_index_num)
=MATCH(lookup_value, lookup_array, match_type)
When we use
MATCH together, the MATCH function finds
the lookup_value’s row-index or column-index number and then
passes this information to the
INDEX function, which returns the
information we actually want.
Here’s the first row of functions:
VLOOKUP relies on a reference to the entire database, MyData. It
looks in the left-most column of MyData for the value in cell F3
and returns the results from the 3rd column of MyData. Because
the range_lookup value was omitted, VLOOKUP uses an approximate
match, which requires the data to be sorted in ascending order.
MATCH searches the SKU column for the value in cell F3 and
returns the row-index number where the item is found. (Because
the match_type is omitted, MATCH also performs an approximate
match in this instance.) Then the INDEX function returns the
value for the specified row-index value from the Sales column.
So what if we look for a SKU that doesn’t exist?
Here, both functions still use an approximate match. Therefore,
because the SKU A-25 doesn’t exist in the database, both return
their best guess for the value we want…a guess that is incorrect
in this case.
So what if we require an exact match and we have a missing item?
Here, the final argument added to both VLOOKUP and MATCH tells
the functions to return an exact match. Both versions return
#N/A because SKU A-25 doesn’t exist in our database.
In both cases, using an exact match has both advantages and
disadvantages when compared to an approximate match. The
advantages are that the SKU data doesn’t need to be sorted, and
that if lookup_value isn’t found, we get an error message
(which is usually a good thing). The disadvantage is that
approximate matches calculate more quickly than exact matches.
(If we’re not careful, however, this method gives us bad data
Finally, suppose we want to search our database for the
description of the item, and then return the SKU?
G6: (This cell is grayed out, because we can’t use
VLOOKUP for this task.)
Here, cell H6 illustrates the power of the INDEX-MATCH approach.
With INDEX-MATCH, we can search any column in our database and
return data from any column. But with VLOOKUP, we can search
only in the left-most column of a multi-cell range.
Several years ago I wrote an article that compared VLOOKUP
and INDEX-MATCH in more detail, Excel’s VLOOKUP vs
INDEX-MATCH Is Faster Than VLOOKUP
For years, the common wisdom has been that VLOOKUP calculates
more quickly than INDEX-MATCH. Because that didn’t seem to be
true in my spreadsheets, I performed a variety of tests about
this topic several years ago. The first post of my three-part
series about these test can be found at Use
Excel VBA to Test Report Calculation Times. The series
includes a workbook you can download to perform your own tests.
Here are the results I found:
With unsorted data, VLOOKUP and INDEX-MATCH have about the same
calculation times. That is, INDEX-MATCH is only about 3% faster.
With sorted data and an approximate match, INDEX-MATCH is about
30% faster than VLOOKUP.
With sorted data and a fast technique to find an exact match,
INDEX-MATCH is about 13% faster than VLOOKUP.
Additionally, however, you can use a version of the INDEX-MATCH
technique to calculate MUCH more quickly than with VLOOKUP.
Suppose you have a large table with many columns of product
information. And suppose you want to look up a specific SKU in
the table and return information about it from a variety of
columns within the table.
If you use VLOOKUP you must look up the same SKU for each column
of information you need. Those duplicate lookups take a long
time to perform.
But if you use an INDEX-MATCH approach, you could set up one
MATCH formula that returns only the row-index number for the
product that interests you. Then you can use any number of INDEX
formulas that get their row-index number from the cell with that
single MATCH formula. Both MATCH and VLOOKUP take about the same
time to calculate. But INDEX works almost instantly. So if you
want to return ten items for a SKU, the VLOOKUP method will take
roughly ten times longer than the INDEX-MATCH approach.
In short, using INDEX-MATCH is more flexible and faster than
VLOOKUP. Try it. You’ll like it.
download the workbook here.)