Excel Lookup Functions
Why INDEXMATCH 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...
Excel’s
VLOOKUP
function is more popular than the
INDEXMATCH
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. INDEXMATCH is much more flexible than Excel's "lookup"
functions.
2. At its worst, INDEXMATCH is slightly faster than VLOOKUP; at
its best, INDEXMATCH is manytimes 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 INDEXMATCH. 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 INDEXMATCH. 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 INDEXMATCH
The following figure shows a small database in columns AC and
several uses of both VLOOKUP and INDEXMATCH in the remaining
columns. To make formulas easier to talk about, I’ve assigned
the following range names:
MyData =Sheet1!$A$3:$C$6
SKU =Sheet1!$A$3:$A$6
Desc =Sheet1!$B$3:$B$6
Sales =Sheet1!$C$3:$C$6
And here’s Sheet1:
(You can
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 INDEX and MATCH together, the MATCH function finds
the lookup_value’s rowindex or columnindex number and then
passes this information to the INDEX function, which returns the
information we actually want.
Here’s the first row of functions:
G3: =VLOOKUP(F3,MyData,3)
H3: =INDEX(Sales,MATCH(F3,SKU))
VLOOKUP relies on a reference to the entire database, MyData. It
looks in the leftmost 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 rowindex 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 rowindex value from the Sales column.
So what if we look for a SKU that doesn’t exist?
G4: =VLOOKUP(F4,MyData,3)
H4: =INDEX(Sales,MATCH(F4,SKU))
Here, both functions still use an approximate match. Therefore,
because the SKU A25 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?
G5: =VLOOKUP(F5,MyData,3,FALSE)
H5: =INDEX(Sales,MATCH(F5,SKU,0))
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 A25 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
quicker.)
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.)
H6: =INDEX(SKU,MATCH(F6,Desc,0))
Here, cell H6 illustrates the power of the INDEXMATCH approach.
With INDEXMATCH, we can search any column in our database and
return data from any column. But with VLOOKUP, we can search
only in the leftmost column of a multicell range.
Several years ago I wrote an article that compared VLOOKUP
and INDEXMATCH in more detail, Excel’s VLOOKUP vs
INDEXMATCH Functions.
INDEXMATCH Is Faster Than VLOOKUP
For years, the common wisdom has been that VLOOKUP calculates
more quickly than INDEXMATCH. 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 threepart
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 INDEXMATCH have about the same
calculation times. That is, INDEXMATCH is only about 3% faster.
With sorted data and an approximate match, INDEXMATCH is about
30% faster than VLOOKUP.
With sorted data and a fast technique to find an exact match,
INDEXMATCH is about 13% faster than VLOOKUP.
Additionally, however, you can use a version of the INDEXMATCH
technique to calculate MUCH more quickly than with VLOOKUP.
Here’s how:
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 INDEXMATCH approach, you could set up one
MATCH formula that returns only the rowindex number for the
product that interests you. Then you can use any number of INDEX
formulas that get their rowindex 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 INDEXMATCH approach.
In short, using INDEXMATCH is more flexible and faster than
VLOOKUP. Try it. You’ll like it.
(You can
download the workbook here.)
