Day 9 — INDEX, MATCH and XLOOKUP

The lookup that works in any direction and survives someone inserting a column.

Two functions, one job

MATCH finds where something is. INDEX fetches what is at a position. Together they do everything VLOOKUP does, without its limits.

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

MATCH on its own

=MATCH(what to find, where to look, 0 for exact)

Order 1009 is the 9th item of the range A2:A16. Not row 9 of the sheet — the 9th item counting from the start of the range you gave it. That distinction matters.

INDEX on its own

=INDEX(range, which item)

Together

MATCH works out the position, INDEX fetches from it. Read it inside out.

Same answers as VLOOKUP gave yesterday, but now the lookup column and the return column are named separately — so no column counting, and inserting a column cannot break it.

Looking to the left, which VLOOKUP cannot do

Search column D, return from column A. INDEX and MATCH do not care about direction at all.

Two-way lookup

Give INDEX both a row number and a column number and you can pick any cell by name.

=INDEX(whole table, row MATCH, column MATCH)

Now both the row and the column are found by name. Rearrange the columns however you like and every one of these still returns the right value.

XLOOKUP — the modern replacement

=XLOOKUP(what to find, where to look, what to return, [if not found])

Three plain arguments: find this, in here, return from there. It defaults to exact match, it looks in any direction, and the fourth argument replaces the whole IFERROR wrapper.

Check your Excel version first XLOOKUP needs Microsoft 365 or Excel 2021. In Excel 2019 or earlier it does not exist, and a file using it will show #NAME? on those machines. If you share workbooks with people on older versions, INDEX and MATCH remain the safe choice — which is exactly why interviews still ask about them.

Side by side

Three ways to the same answer. VLOOKUP is shortest to type and breaks most easily. XLOOKUP is clearest but needs a recent Excel. INDEX and MATCH work everywhere and never break.

A practical lookup on the Staff sheet

This lesson uses the Staff sheet below.

Try these yourself

Use INDEX and MATCH to find the amount for order 1014.
Use INDEX and MATCH to find the order ID for the first Keyboard sale.
Build a two-way lookup that returns the Qty for order 1007.
Do the same lookup with XLOOKUP.
On the Staff sheet, find Priya Singh’s department, returning "Unknown" if the name is missing.

← PreviousDay 8 — VLOOKUP and why it disappoints Next →Day 10 — Errors, data validation and conditional formatting