Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, 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” functions.
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:
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:
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 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:
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 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?
G4: =VLOOKUP(F4,MyData,3)
H4: =INDEX(Sales,MATCH(F4,SKU))
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?
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 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 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 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 Functions.
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. 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 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.
