INDEX MATCH vs XLOOKUP: Which Lookup to Use

Excel & Google Sheets

How it works

Both formulas do the same job: find E2 in column A, return the same row from column C — and both can look left, survive inserted columns, and skip VLOOKUP's column counting. XLOOKUP does it in one readable function with exact match as the default and a built-in if-not-found argument, so it should be your default in Excel 2021/365 and Google Sheets. INDEX MATCH earns its keep in two cases. First, compatibility: it works in every Excel ever shipped, so files that circulate to Excel 2019 or older need it (XLOOKUP shows #NAME? there). Second, true two-way lookups: =INDEX(B2:C5,MATCH(E2,A2:A5,0),MATCH("Price",B1:C1,0)) finds the row AND the column by name — one INDEX with two MATCHes, which is cleaner than nesting two XLOOKUPs.

E2

The value to find — a SKU, name, or invoice number.

A2:A5

The column to search. INDEX MATCH splits this into MATCH(E2,A2:A5,0).

C2:C5

The column to return from. INDEX MATCH wraps it: =INDEX(C2:C5,MATCH(...)).

When to use it

Standardize on XLOOKUP for everyday lookups in modern Excel and Google Sheets. Keep INDEX MATCH for workbooks shared with Excel 2019 or older, and for two-way lookups where both the row and the column are found by name.

Common mistakes

• ✕MATCH without the 0 third argument.

  ✓=INDEX(C2:C5,MATCH(E2,A2:A5)) defaults to approximate match and silently returns wrong rows on unsorted data. Always close with 0: MATCH(E2,A2:A5,0).

• ✕INDEX and MATCH ranges starting on different rows.

  ✓MATCH(E2,A2:A5,0) with INDEX(C1:C4,...) is off by one — MATCH returns a position, not a row number. Both ranges must cover exactly the same rows.

• ✕Shipping XLOOKUP to old-Excel users.

  ✓Excel 2019 and older shows #NAME? on every XLOOKUP. If the file leaves your team, use INDEX MATCH — it works everywhere, all the way back.