XLOOKUP vs INDEX MATCH: which should I use?

Use XLOOKUP in new Microsoft 365, Excel 2021, Excel for the web, and current Google Sheets files. It defaults to exact match and can search last-to-first. Keep INDEX MATCH when the file must open in Excel 2019 or earlier—XLOOKUP becomes #NAME? there—or when one MATCH helper feeds cells that cannot spill. XLOOKUP vs INDEX MATCH, by file: - New 365 / Excel 2021 / current Sheets: XLOOKUP - Shared Excel 2019 or 2016: INDEX MATCH with match_type 0 - Invoice or print layout with gaps between return cells: INDEX MATCH helper - Contiguous spill of several columns in 365: XLOOKUP return_array Do not rewrite a stable INDEX MATCH model just to look modern.

Is XLOOKUP better than INDEX MATCH?

XLOOKUP is shorter for a single lookup, defaults to exact match, and handles missing keys without wrapping IFNA. INDEX MATCH still wins on compatibility and on layouts that cannot use a spill range. "Better" means the formula that opens for every person who receives the workbook and returns the field you asked for. When XLOOKUP is better: - New workbooks on Microsoft 365 or Excel 2021 - You need the last matching row (search_mode -1) - You want if_not_found in the formula When INDEX MATCH is better: - Recipients on Excel 2019 or earlier - One MATCH position reused across non-adjacent cells If both run, pick the formula the shared file can open.

Does INDEX MATCH work in Excel 2016 and 2019?

Yes. INDEX and MATCH have been in Excel for decades, which is why teams still search this comparison. XLOOKUP needs Microsoft 365, Excel 2021, Excel for the web, or current Google Sheets. Open an XLOOKUP file in Excel 2019 and every converted cell shows #NAME?—a version error, not a lookup error. Where INDEX MATCH still runs: - Excel 2016 and 2019 desktop (Windows and Mac) - Older Excel for Mac builds that never got XLOOKUP - Files you export from Sheets into desktop Excel Where XLOOKUP runs: - Microsoft 365, Excel 2021, and Excel for the web - Current Google Sheets If you cannot control the recipient's build, keep INDEX MATCH with match_type 0.

XLOOKUP vs INDEX MATCH in Google Sheets

Current Google Sheets has both. For a Sheets-only file, XLOOKUP is usually the clearer default: exact match, if_not_found, and last-to-first search. INDEX MATCH remains useful when the same file may be downloaded and opened in Excel 2019 or earlier, where XLOOKUP will not calculate. XLOOKUP vs INDEX MATCH in Google Sheets: - Sheets-only, current browsers: XLOOKUP - Sheets file that finance still opens in old Excel: INDEX MATCH - Left lookup in either app: both work; skip VLOOKUP - Missing keys: XLOOKUP if_not_found, or IFNA around INDEX MATCH Do not assume every desktop Excel copy can read an XLOOKUP exported from Sheets.

Which is faster, XLOOKUP or INDEX MATCH?

On a few thousand rows you will not feel a difference. Speed shows up in structure, not in the function name. Repeating XLOOKUP to pull five fields from the same ID scans the lookup column five times. One MATCH helper plus five INDEX formulas scans once. XLOOKUP spilling a contiguous return_array is also one scan—and is the better 365 pattern when the layout allows it. Which is faster, XLOOKUP or INDEX MATCH: - Single return, modern Excel: effectively a tie - Last matching row: XLOOKUP search_mode -1 - Many non-adjacent returns from one key: MATCH once, INDEX many times - Contiguous multi-column return in 365: one XLOOKUP with a wide return_array

Learn · lookup

XLOOKUP vs INDEX MATCH: which one should you use?

Updated 2026-08-21

Use XLOOKUP in new Microsoft 365, Excel 2021, Excel for the web, and current Google Sheets files. Keep INDEX MATCH when the workbook must open in Excel 2019 or earlier, or when one MATCH helper has to feed several non-adjacent return cells that cannot spill. This page is for people choosing between those two lookups on the same table—not a general XLOOKUP tutorial. The rest shows how the formulas differ on sample data, where XLOOKUP wins, where INDEX MATCH still wins, and what to check after you switch.

Pick the function from the file, not the blog post

Situation	Use
Microsoft 365, Excel 2021, Excel for the web, current Sheets	XLOOKUP (default for new formulas)
Shared with Excel 2016 / 2019 / unknown desktop versions	INDEX MATCH with match_type 0
Last matching row in a log	XLOOKUP with search_mode -1
Non-adjacent return cells that cannot spill	One MATCH helper, then INDEX

Opening an XLOOKUP workbook in Excel 2019 shows #NAME? in every converted cell. That is a version problem, not a lookup problem.

Same lookup, two formulas

Lookup SKU in E2. When it is W-100, both formulas should return 12.5.

A (SKU)	B (Price)	C (Warehouse)
W-100	12.50	East
W-200	19.00	West

XLOOKUP (exact match is the default):

=XLOOKUP(E2, A2:A100, B2:B100, "")

INDEX MATCH (write 0 or you get an approximate match):

=INDEX(B2:B100, MATCH(E2, A2:A100, 0))

Sample check: put W-999 in E2. Bare INDEX MATCH returns #N/A. XLOOKUP with the fourth argument returns a blank. Omit MATCH’s third argument and you are no longer doing the same job as default XLOOKUP.

What actually changes (XLOOKUP vs INDEX MATCH)

Behavior	XLOOKUP	INDEX MATCH
Lookup direction	Any lookup array and any return array; vertical or horizontal	MATCH finds the position; INDEX returns that cell—left lookups are fine
Return value	Point at the return array; can spill several columns	One cell per INDEX; several INDEX calls can share one MATCH
Default match	Exact if match_mode is omitted	MATCH approximates if you omit match_type—write 0
Error handling	if_not_found argument	#N/A unless you wrap IFNA
Array formulas	Native dynamic arrays; one formula can return a block	Works in 365 without CSE; older Excel may still need it
Compatibility	Excel 2021 / 365 / web—not Excel 2019 and earlier	Excel 2010+; the safe shared-file option
Excel vs Sheets	Fine in current Sheets; export to old Excel may #NAME?	Stable in Sheets and in old desktop Excel

The MATCH trap is the same class of bug as omitted FALSE on VLOOKUP: you think you asked for an exact key, and Excel nearest-matches a sorted (or not) list. XLOOKUP does not do that unless you set match_mode on purpose.

Example 1 — last matching row (XLOOKUP wins)

Status log. Lookup customer C-41 in E2. You want the last status, Shipped—not the first Open.

A (Date)	B (Customer)	C (Status)
2026-08-01	C-41	Open
2026-08-12	C-41	Shipped
2026-08-03	C-88	Open

XLOOKUP with search_mode -1 (search last to first):

=XLOOKUP(E2, B2:B100, C2:C100, "", 0, -1)

INDEX MATCH finds the first C-41 and returns Open:

=INDEX(C2:C100, MATCH(E2, B2:B100, 0))

Sample check: E2=C-41 must be Shipped. E2=C-00 should be blank from XLOOKUP’s if_not_found. Getting last-match out of INDEX MATCH needs extra machinery (LOOKUP/SUMPRODUCT or a helper). That is the XLOOKUP win—not “newer,” but one argument.

Example 2 — Excel 2019 left lookup (INDEX MATCH wins)

Employee IDs sit in column C; names to return sit to the left in A. Both functions can look left. Recipients are on Excel 2019, so XLOOKUP is not a function they have.

A (Name)	B (Dept)	C (EmpID)
Maya Chen	Finance	E-104
Jon Park	Ops	E-221

XLOOKUP (fine on 365; #NAME? on Excel 2019):

=XLOOKUP(E2, C2:C100, A2:A100, "")

INDEX MATCH (opens on 2019):

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

Sample check: E2=E-104 returns Maya Chen. The win here is not syntax—it is that the shared file still calculates. Confirm MATCH’s third argument is 0.

Example 3 — non-adjacent returns, no spill (INDEX MATCH wins)

Invoice layout: price in C12, warehouse in F12, stock in H12. Those cells are not next to each other. A spill from C12 would overwrite D12 and E12.

A (SKU)	B (Price)	C (Warehouse)	D (Stock)
W-100	12.50	East	40
W-200	19.00	West	12

One MATCH in J2, then three INDEX formulas:

=MATCH(E2, A2:A100, 0)
=INDEX(B2:B100, $J$2)
=INDEX(C2:C100, $J$2)
=INDEX(D2:D100, $J$2)

Do not drop this in C12 on that invoice:

=XLOOKUP(E2, A2:A100, B2:D100)

In Microsoft 365, that wide return_array is the right pattern when C12:E12 are empty—one scan, three columns. On a print layout with gaps, it is a layout bug. Three separate XLOOKUPs work but scan the SKU column three times. Sample check: E2=W-100 → 12.50 / East / 40 in C12, F12, and H12 only.

Switching without creating new bugs

Lookup array and return array must be the same height. INDEX MATCH already forces that; XLOOKUP will return #VALUE! if they differ.
Type 0 as MATCH’s third argument. Do not carry VLOOKUP FALSE into XLOOKUP’s 4th argument—that slot is if_not_found.
Prefer XLOOKUP’s if_not_found on new formulas. Prefer IFNA on leftover INDEX MATCH. IFERROR also hides #REF! and #VALUE!.
After paste, test a known hit, a known miss, and one row with extra spaces or a numeric-as-text ID.

Generate the formula, then run the sample checks

If the layout is clear but the argument order is not, describe the lookup cell, lookup column, and return column in plain English. Use Excel dialect for Microsoft 365, or Sheets dialect when the file lives in Google Sheets.

Modern Excel: XLOOKUP formula generator. Need INDEX MATCH for an older workbook? Excel formula generator. Always verify against a row you already know is correct before filling a whole column.

XLOOKUP vs INDEX MATCH FAQs

XLOOKUP vs INDEX MATCH: which should I use?: Use XLOOKUP in new Microsoft 365, Excel 2021, Excel for the web, and current Google Sheets files. It defaults to exact match and can search last-to-first. Keep INDEX MATCH when the file must open in Excel 2019 or earlier—XLOOKUP becomes #NAME? there—or when one MATCH helper feeds cells that cannot spill. XLOOKUP vs INDEX MATCH, by file: - New 365 / Excel 2021 / current Sheets: XLOOKUP - Shared Excel 2019 or 2016: INDEX MATCH with match_type 0 - Invoice or print layout with gaps between return cells: INDEX MATCH helper - Contiguous spill of several columns in 365: XLOOKUP return_array Do not rewrite a stable INDEX MATCH model just to look modern.
Is XLOOKUP better than INDEX MATCH?: XLOOKUP is shorter for a single lookup, defaults to exact match, and handles missing keys without wrapping IFNA. INDEX MATCH still wins on compatibility and on layouts that cannot use a spill range. "Better" means the formula that opens for every person who receives the workbook and returns the field you asked for. When XLOOKUP is better: - New workbooks on Microsoft 365 or Excel 2021 - You need the last matching row (search_mode -1) - You want if_not_found in the formula When INDEX MATCH is better: - Recipients on Excel 2019 or earlier - One MATCH position reused across non-adjacent cells If both run, pick the formula the shared file can open.
Does INDEX MATCH work in Excel 2016 and 2019?: Yes. INDEX and MATCH have been in Excel for decades, which is why teams still search this comparison. XLOOKUP needs Microsoft 365, Excel 2021, Excel for the web, or current Google Sheets. Open an XLOOKUP file in Excel 2019 and every converted cell shows #NAME?—a version error, not a lookup error. Where INDEX MATCH still runs: - Excel 2016 and 2019 desktop (Windows and Mac) - Older Excel for Mac builds that never got XLOOKUP - Files you export from Sheets into desktop Excel Where XLOOKUP runs: - Microsoft 365, Excel 2021, and Excel for the web - Current Google Sheets If you cannot control the recipient's build, keep INDEX MATCH with match_type 0.
XLOOKUP vs INDEX MATCH in Google Sheets: Current Google Sheets has both. For a Sheets-only file, XLOOKUP is usually the clearer default: exact match, if_not_found, and last-to-first search. INDEX MATCH remains useful when the same file may be downloaded and opened in Excel 2019 or earlier, where XLOOKUP will not calculate. XLOOKUP vs INDEX MATCH in Google Sheets: - Sheets-only, current browsers: XLOOKUP - Sheets file that finance still opens in old Excel: INDEX MATCH - Left lookup in either app: both work; skip VLOOKUP - Missing keys: XLOOKUP if_not_found, or IFNA around INDEX MATCH Do not assume every desktop Excel copy can read an XLOOKUP exported from Sheets.
Which is faster, XLOOKUP or INDEX MATCH?: On a few thousand rows you will not feel a difference. Speed shows up in structure, not in the function name. Repeating XLOOKUP to pull five fields from the same ID scans the lookup column five times. One MATCH helper plus five INDEX formulas scans once. XLOOKUP spilling a contiguous return_array is also one scan—and is the better 365 pattern when the layout allows it. Which is faster, XLOOKUP or INDEX MATCH: - Single return, modern Excel: effectively a tie - Last matching row: XLOOKUP search_mode -1 - Many non-adjacent returns from one key: MATCH once, INDEX many times - Contiguous multi-column return in 365: one XLOOKUP with a wide return_array