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

by Charley Kyd 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.

 [click to continue...]

{ 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.

{ 2 trackbacks }

Previous post:

Next post: