INDEX/MATCH Explained: Why Some People Still Swear By It

 INDEX/MATCH Explained: Why Some People Still Swear By It

INDEX/MATCH is two separate functions combined into one formula to look up a value — INDEX pulls a result from a range, and MATCH tells it exactly which position to pull from. It does the same basic job as VLOOKUP or XLOOKUP, but it's more flexible about direction and doesn't break as easily when your spreadsheet changes shape. If you've read my post comparing VLOOKUP and XLOOKUP, think of INDEX/MATCH as the formula that came before both of them and, for a lot of long-time Excel users, never really got replaced.

I mentioned this one twice in my first post because it kept coming up, so here it is properly.

A bit of background on why I still bother with this one. A few years back I inherited a workbook from a colleague who'd left the company, and every single lookup in it was VLOOKUP with a hardcoded column number. Someone had reordered the columns at some point after she left, and nobody noticed the numbers had all gone stale until a report went out with completely wrong figures next to the wrong names. It wasn't a huge disaster, but it was embarrassing, and it happened because the formula was pointing at "the 4th column" instead of pointing at anything meaningful. Rebuilding that workbook with INDEX/MATCH instead of VLOOKUP was what actually got me to properly learn it, rather than just recognising it when I saw it in other people's files.

What does INDEX/MATCH actually do, in plain terms?

INDEX and MATCH do two different jobs that work together.

MATCH finds the position of a value within a range and returns that position as a number — for example, "this value is in the 5th row."

INDEX takes a range and a position number, and returns whatever is sitting in that position.

Put together, MATCH tells INDEX where to look, and INDEX goes and grabs it. Neither function is that useful entirely on its own for lookups, but combined they cover almost everything VLOOKUP does, plus a few things it can't.

The basic structure looks like this:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

A real example — looking up a staff member's department by their employee ID, where the ID column happens to sit to the right of the department column (something VLOOKUP flatly can't handle):

=INDEX(B2:B50, MATCH(D2, C2:C50, 0))

Here, MATCH(D2, C2:C50, 0) finds where the employee ID in D2 sits within the ID column, and INDEX(B2:B50, ...) grabs the department name from that same row position in column B. The 0 at the end of MATCH tells it to look for an exact match — leave that off and MATCH assumes your data is sorted, which almost never ends well.

Why won't my INDEX/MATCH work?

Most of the time it's one of these:

The MATCH type argument is missing or wrong. Leaving out the third argument, or setting it to 1 instead of 0, makes MATCH assume sorted data and return the wrong position silently — no error, just a wrong answer, which is worse than an obvious one.

The INDEX range and the MATCH range aren't the same length. If your return range is 50 rows but your lookup range is only 40, the position MATCH finds might not line up with anything sensible in INDEX.

Mismatched data types, same issue VLOOKUP has — a number stored as text won't match a genuine number, even if it displays identically.

Merged cells anywhere in the range. MATCH and INDEX both get confused by merged cells, and the position count throws out completely.

How is this different from VLOOKUP?

The core difference is that VLOOKUP is locked into searching the leftmost column of a range and always returning something to the right of it. INDEX/MATCH doesn't have that restriction at all, because you choose the lookup range and the return range completely independently — they don't even need to be next to each other.

Feature VLOOKUP INDEX/MATCH

Search direction Left to right only Any direction

Ranges need to be adjacent Yes No

Affected by inserted/deleted columns Yes, if column number shifts No, as long as ranges stay intact

Can do a two-way lookup (row + column) Awkward Straightforward with nested INDEX/MATCH

Learning curve Easier Slightly steeper at first

Can INDEX/MATCH look up using two conditions at once?

Yes, and this is one of the places it genuinely outperforms a single VLOOKUP or XLOOKUP formula without extra tricks. Say you've got a table with regions across the top and product names down the side, and you want the value where a specific region meets a specific product — a classic two-way lookup.

=INDEX(B2:F20, MATCH(H1, A2:A20, 0), MATCH(H2, B1:F1, 0))

Here, INDEX is given a full grid rather than a single column, and it's fed two position numbers — one for the row (from matching the product name down the side) and one for the column (from matching the region across the top). This is the sort of thing that used to make me feel genuinely clever the first time I got it working, and then mildly annoyed at myself for not learning it years earlier.

Should I use INDEX/MATCH or XLOOKUP now?

If you've got access to XLOOKUP, it covers most single-lookup situations more simply than INDEX/MATCH does, since it's one function instead of two nested together. For a straightforward "find this, return that" lookup, XLOOKUP is usually the less fiddly option now.

Where INDEX/MATCH still earns its keep:

Older Excel versions without XLOOKUP still support INDEX/MATCH fine — it's been around since Excel's early days.

Two-way lookups with a grid layout, which INDEX/MATCH handles a little more transparently than XLOOKUP's nested approach.

Reading other people's formulas. A lot of finance and reporting templates built before 2021 are full of INDEX/MATCH, and being able to read one at a glance saves a lot of time versus reverse-engineering it from scratch.

Check my post on XLOOKUP/VLOOKUP

Honestly, I don't think there's a wrong answer between INDEX/MATCH and XLOOKUP for new formulas — I still default to INDEX/MATCH out of habit more often than I probably should, mostly because two decades of muscle memory is hard to shake. If you're starting fresh today, XLOOKUP first, INDEX/MATCH once you're comfortable, is a sensible order.

FAQ

Is INDEX/MATCH faster than VLOOKUP? In very large spreadsheets, yes, INDEX/MATCH is generally a bit faster because VLOOKUP has to scan the entire lookup range from the start every time, while INDEX/MATCH can be more efficient depending on how it's structured. For everyday spreadsheets under a few thousand rows, the difference won't be something you notice.

Why does MATCH return #N/A even though I can see the value? Check for extra spaces, mismatched text/number formatting, or a missing exact-match argument (the 0 at the end). These are the same culprits that break VLOOKUP, and they trip up MATCH just as easily.

Can INDEX/MATCH return an entire row instead of one value? Yes — if you leave the column argument out of INDEX and only give it a row position from MATCH, it'll return the whole row as an array. This is more advanced and usually only useful combined with other functions.

Do I need to press Ctrl+Shift+Enter for INDEX/MATCH? Not for a standard single lookup like the examples above — that's a regular formula. You only need Ctrl+Shift+Enter (or it's automatic in newer Excel versions with dynamic arrays) for certain advanced array-based versions of INDEX/MATCH, which most people never need.

Is INDEX/MATCH still worth learning if I already use XLOOKUP? Worth knowing at least the basics, mainly so you can read other people's spreadsheets and handle two-way lookups comfortably. You don't need to make it your default anymore, but not recognising it when you see it will slow you down in a lot of real workplace files. [Internal link: post about building a two-way lookup dashboard with dynamic arrays]