If you’ve read the XLOOKUP guide, you know my position: learn XLOOKUP first, and don’t apologise for it. So why an article on INDEX and MATCH, the pair that ruled the power-user world for twenty years?

Two reasons. You will inherit workbooks built on it — it was the professional’s choice for decades, and those files run payrolls today. And more interestingly: INDEX/MATCH is a lookup with the hood open. XLOOKUP does find-and-fetch in one sealed unit; INDEX and MATCH are the find and the fetch as separate parts, and seeing the parts separately is what makes every lookup — including XLOOKUP — stop being magic.

The two parts

MATCH answers one question: where?

=MATCH("Widgets", A2:A100, 0)     → 7

Find “Widgets” in this column; it’s the 7th item. Not the value — the position. (The 0 means exact match. Always write it; the default is the same approximate-match trap VLOOKUP set for a generation.)

INDEX answers the other: what’s at?

=INDEX(C2:C100, 7)                → £2,340

Give me the 7th item of this column. That’s the entire function: a range and a position in it.

Snap them together and the position flows from one into the other:

=INDEX(C2:C100, MATCH("Widgets", A2:A100, 0))

What’s in the price column at the row where the product column says Widgets? — a lookup, assembled from parts you can test separately. And that’s the debugging superpower: when a lookup fails, put the MATCH alone in a spare cell. Position or #N/A, the fault is instantly on one side of the hood or the other — almost always the MATCH, and almost always a space or a text-number.

MATCH scans… → position 3 Gaskets Flanges Widgets Sprockets £1,100 £860 £2,340 £790 ← INDEX, row 3 the find and the fetch, as separate parts — testable separately
MATCH walks the left column and reports "position 3"; INDEX collects whatever sits third in the right column. Two small ideas, one lookup.

Where the open hood earns its keep

Two-way lookups read beautifully. One MATCH finds the row, a second finds the column, and INDEX takes the grid and both positions:

=INDEX(DataGrid, MATCH(A2, Products, 0), MATCH(B2, Months, 0))

Row-finder, column-finder, grid — a crosshair, written exactly the way you’d describe it. (XLOOKUP’s nested version does the same job; this one, many people find easier to read.)

One MATCH can feed many INDEXes. Looking up eight columns for the same customer? Put the MATCH in a helper cell once, and let eight cheap INDEXes share it. Tidier to audit, and on big sheets genuinely faster — the expensive part of any lookup is the search, and this does one search instead of eight.

It never counts columns. Like XLOOKUP — and unlike VLOOKUP — the pair points at ranges by reference, so inserting columns breaks nothing. Old workbooks built on INDEX/MATCH have quietly survived renovations that killed their VLOOKUP cousins. There’s a lesson in that beyond lookups.

Translating the inheritance

When you meet it in the wild:

=INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0))

is precisely

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C)

Fetch column, find column — the same two ranges, opposite order. No need to rewrite working formulas; every need to understand them before touching them. The rule from the VLOOKUP days applies unchanged: read the old dialect, write in the new one.

The honest recommendation

Nothing here changes the advice: new workbooks, XLOOKUP, no apology. Learn INDEX/MATCH for the three occasions that actually call for it — inherited files, the shared-MATCH efficiency pattern, and colleagues (or Excel versions) that haven’t met XLOOKUP yet. That’s stage four of the path done properly: not collecting lookups like stamps, but knowing what where and what’s-at mean everywhere.

Open the hood once. Every lookup you ever meet afterwards is just those two questions wearing different syntax.