XLOOKUP vs INDEX-MATCH: which one, when, and why

If an interviewer hands you a laptop, this is the skill they’re checking first. VLOOKUP alone marks you as a beginner; knowing when to reach for XLOOKUP versus INDEX-MATCH marks you as someone who’s done real work.

XLOOKUP, the modern default

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Say policies are in column A and premiums in column D. Finding policy “P-1042“‘s premium:

=XLOOKUP("P-1042", A:A, D:D, "Not found")

Why it beats VLOOKUP everywhere: it looks left or right (VLOOKUP only looks right), it doesn’t break when someone inserts a column (no fragile column-number argument), it defaults to exact match (VLOOKUP’s silent default is approximate: the source of a thousand wrong reserves), and the fourth argument handles missing values without wrapping everything in IFERROR.

INDEX-MATCH: the veteran that still matters

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

The same lookup: =INDEX(D:D, MATCH("P-1042", A:A, 0)). Read it inside out: MATCH finds which row the policy sits in; INDEX returns that row from the premium column.

Two reasons this dinosaur survives: older Excel versions (plenty of insurance companies run them. XLOOKUP needs Excel 2021/365), and two-dimensional lookups, where MATCH runs on both axes:

=INDEX(B2:M50, MATCH("P-1042", A2:A50, 0), MATCH("Premium", B1:M1, 0))

That finds the row and the column by name, the pattern behind every rate table lookup you’ll ever build.

The decision rule

Modern workbook, one dimension → XLOOKUP. Unknown Excel version, shared company templates, or two-dimensional tables → INDEX-MATCH. Interview → mention both, use XLOOKUP, and say why. That sentence alone has gotten people offers.

Practice it: the free study dashboard lesson puts both to work on live data, which beats drilling them in isolation.