XLOOKUP & INDEX/MATCH (Modern Lookups)¶

XLOOKUP fixes everything wrong with VLOOKUP. If your Excel has it, prefer it. INDEX/MATCH is the powerful classic alternative that works everywhere.

A clean lookup pulling data across columns in either direction

XLOOKUP Syntax¶

excel

1 line

1=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

excel

3 lines

1=XLOOKUP("P02", E2:E4, F2:F4)              → the price
2=XLOOKUP(A2, IDs, Names, "Not found")      → look LEFT, no problem
3=XLOOKUP(MAX(Sales), Sales, Reps)          → who had the top sale

Why XLOOKUP Wins¶

+Looks **left or right** — no leftmost-column rule

+Defaults to **exact match** (no FALSE to remember)

+Built-in `if_not_found` — no `IFERROR` wrapper needed

+Returns whole rows/columns, searches top-down *or* bottom-up

−VLOOKUP can do none of these cleanly

| Name  | ID |       =XLOOKUP("Ada", A2:A4, B2:B4)
| Ada   | 01 |       → 01   (returns the ID to the RIGHT of the match)
| Grace | 02 |

INDEX/MATCH — The Timeless Combo¶

Available in every Excel version. MATCH finds the position; INDEX returns the value at that position.

excel

1 line

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

excel

1 line

1=INDEX(F2:F4, MATCH("P02", E2:E4, 0))   → the price for P02

MATCH("P02", E2:E4, 0) → 2 (P02 is the 2nd item; 0 = exact).
INDEX(F2:F4, 2) → the 2nd value in the price column.

Two-Way Lookup with INDEX/MATCH¶

INDEX can take both a row and a column position — look up a value by row label and column label at once:

excel

1 line

1=INDEX(data, MATCH(rowKey, rowLabels, 0), MATCH(colKey, colLabels, 0))

Tip

Decision rule: Have XLOOKUP? Use it. On an older/shared file without it? Use INDEX/MATCH. Reserve VLOOKUP for legacy sheets you're maintaining.