Excel XLOOKUP vs INDEX-MATCH: I Tested Both on 100,000 Rows

Which lookup is actually faster — and at 100,000 rows, does the difference even matter? Every Excel forum has a confident answer, usually unburdened by measurement. So I built a 100,000-row table, wrote ten thousand of each formula, and timed full recalculations to find out where the real differences are. Spoiler: speed is the least interesting difference between these two.

📋 In This Guide

• Syntax: the same lookup, two ways
• Readability and maintenance
• Error handling
• The 100,000-row speed test
• Feature-by-feature comparison
• When INDEX-MATCH is still the right call
• Tips
• Warnings
• FAQ
• Sources

Syntax: The Same Lookup, Two Ways

Say column A holds 100,000 order IDs and column C holds amounts, and you want the amount for the ID in E2. With XLOOKUP, that is one function with the arguments in reading order:

=XLOOKUP(E2, A2:A100001, C2:C100001, "Not found")

The classic combination of INDEX and MATCH does the same job in two nested calls:

=INDEX(C2:C100001, MATCH(E2, A2:A100001, 0))

Both look left or right with equal ease — the old VLOOKUP limitation is irrelevant to this fight. XLOOKUP defaults to exact match; MATCH requires you to remember the trailing 0, and forgetting it silently returns wrong answers on unsorted data, which is the most expensive kind of spreadsheet bug: the one that produces a plausible number.

Readability and Maintenance

XLOOKUP reads like a sentence: find this, in here, return from there, else say this. INDEX-MATCH reads inside-out, and every new colleague who inherits your workbook pays a small tax decoding it. Six months later, that tax applies to you too. XLOOKUP also returns whole rows or multiple columns in one spilled formula by widening the return array — something INDEX-MATCH needs extra machinery for. For two-dimensional lookups, XLOOKUP can be nested (one for the row, one for the column), while INDEX with two MATCH calls remains a perfectly good classic. Where INDEX-MATCH earns respect is flexibility: because MATCH is a separate step, you can reuse one MATCH result across five INDEX columns and pay the search cost once — a genuinely elegant optimization pattern.

Error Handling

When a lookup value is missing, both return #N/A — but XLOOKUP has a built-in if_not_found argument, so the fallback lives inside the function. INDEX-MATCH needs an IFERROR wrapper:

=IFERROR(INDEX(C2:C100001, MATCH(E2, A2:A100001, 0)), "Not found")

The subtle difference: IFERROR swallows every error, including typos like a misspelled range name or a #REF! from a deleted column, and quietly reports “Not found” instead. XLOOKUP’s if_not_found only catches the no-match case, so real formula errors still surface where you can see them. That is the correct behavior, and it is a point people underrate. Microsoft’s own guide to lookup functions covers the #N/A cases both ways.

The 100,000-Row Speed Test

A quick honesty note before numbers: Excel recalc timing depends heavily on CPU, RAM, Excel version, multithreading settings, and what else the workbook contains. Treat everything below as indicative results from one mid-range Windows laptop, not a lab benchmark — your machine will differ, and that is fine, because the pattern is what transfers.

Step 1: Build the dataset

I generated 100,000 rows of unique order IDs with random amounts and dates. IDs were text strings in random order, which forces true linear searching for exact-match lookups — the worst realistic case.

Step 2: Write 10,000 of each formula

One sheet got 10,000 XLOOKUP formulas, an identical sheet got 10,000 INDEX-MATCH formulas, each looking up a random ID against the full 100,000-row range. Same targets, same ranges, same workbook settings.

Step 3: Time full recalculations

I forced full recalcs (Ctrl+Alt+F9) several times per sheet and compared typical timings. Both sheets recalculated in the same rough band — a few seconds each, and in my testing the gap between XLOOKUP and INDEX-MATCH stayed within the run-to-run noise of repeated recalcs. Neither was consistently the winner for unsorted exact-match work. What did move the needle, dramatically: sorting the lookup column and using binary search — XLOOKUP’s search_mode of 2, or MATCH with match type 1 — cut recalc time by an order of magnitude, from seconds to well under a second. And referencing whole columns like A:A instead of exact ranges made both approaches measurably slower. The lever is the match mode and the range, not the function name.

Feature-by-Feature Comparison

*Indicative timings from one machine; hardware and Excel version change the absolute numbers but not the ranking.

When INDEX-MATCH Is Still the Right Call

Microsoft is explicit that XLOOKUP is not available in Excel 2019, Excel 2016, or anything older. If your file will be opened by someone on a perpetual-license Excel 2019 install — still common in corporate environments and older institutional machines — every XLOOKUP cell shows a #NAME? error on their screen. For templates you distribute publicly, workbooks shared with clients whose Excel version you cannot control, or long-lived files in conservative IT environments, INDEX-MATCH remains the compatibility-safe choice. It is also worth keeping when you want the one-MATCH-many-INDEX optimization pattern, or when you are maintaining a large legacy model where a wholesale rewrite adds risk for zero functional gain.

Tips

• Sort your lookup column and switch to binary search mode when recalc time actually hurts — it beats any XLOOKUP-vs-INDEX-MATCH micro-optimization.
• Reference exact ranges or Table columns, not whole columns like A:A.
• When one lookup value feeds several output columns, do the MATCH once in a helper cell and point multiple INDEX formulas at it.
• Use XLOOKUP’s fourth argument instead of wrapping it in IFERROR — you will keep real errors visible.
• Converting a legacy workbook? Change formulas sheet by sheet and recalc between steps so a regression is easy to localize.

Warnings

• XLOOKUP formulas break with #NAME? in Excel 2019 and older — check every recipient’s version before you standardize on it.
• MATCH without the third argument defaults to approximate match and returns confidently wrong values on unsorted data.
• Binary search modes require correctly sorted data; on unsorted data they return wrong results without any error.
• Benchmark numbers you see online (including mine) reflect one machine and one workbook — always test on your own file before making performance decisions.

FAQ

Is XLOOKUP faster than INDEX-MATCH?

For exact-match lookups on unsorted data, no meaningful difference showed up in repeated 100,000-row recalc tests — both landed in the same range, within run-to-run noise. Performance should not be the reason you pick one; compatibility and maintainability should be.

Does XLOOKUP work in Excel 2019?

No. Per Microsoft’s documentation, XLOOKUP requires Microsoft 365 or Excel 2021 and newer. In Excel 2019, 2016, and earlier, the cell displays a #NAME? error, so use INDEX-MATCH (or VLOOKUP) in any file those versions must open.

Should I rewrite my old INDEX-MATCH formulas as XLOOKUP?

Only when you touch them anyway, and only if everyone opening the file is on a supported version. Working formulas in a stable file are not a problem to fix; rewriting hundreds of them introduces risk for essentially no speed benefit. Standardize on XLOOKUP for new work instead.

Sources

• Microsoft Support: XLOOKUP function
• Microsoft Support: INDEX function
• Microsoft Support: MATCH function
• Microsoft Support: Look up values with VLOOKUP, INDEX, or MATCH

?뱦 Related reading

• 2026 Best AI Writing Assistants for Long-Form Content
• How to Fact-Check an AI-Generated Answer
• 2026 Best AI Tools for Everyday Productivity