# Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

by on March 22, 2012

If Google search results are any indication, Excel’s VLOOKUP function is about 30 times more popular than the INDEX-MATCH function. This is too bad, because …

1. INDEX-MATCH is much more flexible than VLOOKUP.

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_numrange_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 a five-part series compared VLOOKUP and INDEX-MATCH in more detail. The first post of the series is: 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.

{ 3 comments }

Amicron March 28, 2012 at 10:35 am

Good tutorial. I agree with you when it comes to EXPERT users, but VLOOKUP is still the best solution for novices who aren’t dealing with huge spreadsheets and want a quick and easy solution.

Pete_B March 29, 2012 at 12:23 am

Hi Charley,

I’ve been working with large spreadsheets and some autocalculation columns and have been looking for ways to improve the calculation times. I also noticed that vlookup is horribly slow (so much so that I add the columns in small groups now and flatten the results as soon as they’re generated) and have been looking for ways to automate things again.

Instead of using INDEX-MATCH I’ve been working with OFFSET-MATCH:
J2 =OFFSET(C\$2,MATCH(F3,SKU),0)
which I’m guessing works with an equivalent speed to INDEX-MATCH. Using offset allows me to also specify a column displacement for some of my spreadsheets.

What I haven’t yet found is a way to search an area instead of just a column; for example if a product has a number of different articles, in the product table these unique SKU IDs are listed in a number of columns after the product ID. Attempting to look up the product name for a SKU ID requires either nesting lookups with IFs, or having as many lookup columns as there are SKU columns; the only alternative I’ve come up with so far is to create another sheet with one column for SKU IDs and all the product information cloned multiple times (read: laborious creation of a second parallel spreadsheet).

Do you have any ideas for ‘area’ searches?

Cheers,
Pete

Charley Kyd March 29, 2012 at 8:17 am

Pete,

In some cases like the one you describe, I’ll set up a helper column. That is, I’ll create one column that combines the data from several columns, then search that one column with my MATCH function.

For example, if your SKUs could be in column D or E or F, I would enter a formula like this in cell G5…
=\$D5&\$E5&\$F5
…and then copy it down the column.

Keep in mind, however, that MATCH tries to match the entire contents of a cell. So if you combine two SKUs in one cell, MATCH won’t find “S123x” in a cell containing “S123xS124x”.

Finally, you could avoid the helper column by doing something similar with an array formula. For example, if “S123x” is in one of the ranges specified (and the other two cells in its row are empty) this works if you array-enter it:
=MATCH(“S123x”,D4:D9&E4:E9&F4:F9,0)

To array-enter a formula, of course, you hold down the Ctrl and Shift keys before you press Enter.

{ 1 trackback }