XLOOKUP vs INDEX-MATCH on 100,000 Rows: What Actually Slows Excel Down

Which one recalculates faster on a 100,000-row table? Every Excel forum has a confident answer, usually unburdened by measurement. When I went digging through Microsoft published guidance on lookup calculation cost, the picture that came back had almost nothing to do with the function name and everything to do with the search algorithm underneath it. Speed is the least interesting difference between these two.

📋 Jump to a Section

• Syntax: the same lookup, two ways
• Readability and maintenance
• Error handling
• What Microsoft documents about lookup speed
• Measure it in your own workbook
• 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 does not. Microsoft spells out why that matters. Match type 1 is the default and it assumes an ascending sort. Feed it unsorted data and it returns an incorrect answer without complaining. Forgetting the trailing 0 produces the most expensive kind of spreadsheet bug. The plausible wrong 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 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 several columns in one spilled formula by widening the return array. INDEX-MATCH needs extra machinery for the same result. For two-dimensional lookups XLOOKUP can be nested, one for the row and one for the column, while INDEX with two MATCH calls remains a perfectly good classic. Where INDEX-MATCH earns real respect is structural. Because MATCH is a separate step, you can store one MATCH result and reuse it across five INDEX formulas, paying the search cost once. Microsoft recommends exactly that pattern for exact-match lookups spanning multiple result columns.

Error Handling

When a lookup value is missing, both return #N/A. 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. A misspelled range name or a #REF! from a deleted column quietly becomes “Not found”. XLOOKUP’s if_not_found only catches the no-match case, so genuine formula errors still surface where you can see them. That is the correct behavior and people underrate it. Microsoft’s own guide to lookup functions walks through the #N/A cases both ways.

XLOOKUP vs INDEX-MATCH Speed: What Microsoft Actually Documents

When I checked Microsoft’s published performance material, no head-to-head XLOOKUP against INDEX-MATCH timing table turned up anywhere in it. Anyone quoting one is quoting their own laptop. What Microsoft does publish, in its performance obstructions guidance, is the mechanism. That turns out to be more useful than any single number.

Four documented points do most of the work here.

• Exact-match cost is proportional to cells scanned. Microsoft states that with the exact match option, calculation time is proportional to the number of cells scanned before a match is found. Over large ranges that time becomes significant. Neither function escapes this, because both are doing the same linear walk.
• Sorted data plus approximate match behaves like a binary search. Microsoft describes lookup time on sorted data using approximate match as fast and not significantly increased by the length of the range. XLOOKUP reaches that path with search_mode 2, MATCH with match type 1. Same idea, different spelling.
• The documented gap between the two styles is small and points the other way. Microsoft notes VLOOKUP is approximately 5 percent faster than a MATCH plus INDEX combination, while adding that the flexibility of MATCH and INDEX often saves far more time than that margin. A single-digit percentage is not a reason to rewrite a workbook.
• INDEX is not volatile. Microsoft explicitly corrects the old myth here. INDEX appears on its published list of functions once documented as volatile that turned out not to be. OFFSET and INDIRECT are the volatile ones, and they recalculate on every pass.

One more line from the calculation performance overview deserves to be taped to your monitor: the most important factor influencing Excel calculation speed is still the way the worksheet is designed and built. Function choice is downstream of that.

Measure It in Your Own Workbook

Your hardware, Excel build, multithreading settings and workbook contents all move the numbers. So measure locally. Microsoft publishes the full method, and the steps below follow it.

Step 1: Switch calculation to manual

Formulas → Calculation Options → Manual. In manual mode Excel recalculates only when you ask, and the status bar shows Calculate when the workbook is dirty. Without this, background recalcs contaminate every reading you take. Note that these settings apply at application level, not per workbook.

Step 2: Build two sheets that differ in one thing

Put the same 100,000-row table and the same batch of lookup values on both sheets. One sheet gets XLOOKUP formulas, the other gets INDEX-MATCH. Identical ranges, identical targets. Keep lookups and data on the same sheet, which Microsoft lists as a speed-up in its own right.

Step 3: Force a full calculation and time it

Ctrl+Alt+F9 forces a full calculation of all formulas. Shift+Ctrl+Alt+F9 rebuilds the dependency tree first. For real numbers rather than stopwatch guesses, Microsoft supplies a MicroTimer function built on the Windows high-resolution timer plus small VBA routines that wrap Application.CalculateFull and Application.Calculate. Copy those into the workbook and run FullCalcTimer.

Step 4: Repeat, then average

Microsoft warns directly that timings do not repeat exactly, and that a second calculation of the same thing may run faster than the first. Windows is multitasking underneath you. Run each measurement several times and average the results. If your two averages sit inside that spread, you have your answer: the functions are not your bottleneck.

Step 5: Change one variable at a time

Now vary the things Microsoft says actually matter. Sort the lookup column and switch both formulas to their binary search mode. Swap a whole-column reference such as A:A for a structured table reference. Move the lookup table onto a different sheet and back. Those deltas will dwarf the XLOOKUP-versus-INDEX-MATCH delta, which is the whole point of running the exercise yourself.

Feature-by-Feature Comparison

No published vendor benchmark compares these two head to head. Treat the last row as a description of mechanism, and produce your own timings using the procedure above.

When INDEX-MATCH Is Still the Right Call

Microsoft is blunt about this in the XLOOKUP reference: XLOOKUP is not available in Excel 2016 and Excel 2019. Open a workbook containing it on one of those perpetual-license installs, still common across corporate and institutional machines, and every XLOOKUP cell shows #NAME?.

That makes INDEX-MATCH the compatibility-safe choice for templates you distribute publicly, workbooks shared with clients whose Excel version you cannot control, and long-lived files in conservative IT environments. Keep it too when you want the one-MATCH-many-INDEX pattern, or when a large legacy model would gain nothing functional from a wholesale rewrite.

Tips

• Sort the lookup column and move to approximate or binary search mode when recalc time genuinely hurts. Microsoft describes SORT itself as fast, and the payoff dwarfs any function-choice tweak.
• Reference exact ranges or structured table columns instead of whole columns like A:A. Array-style calculations over a whole column process empty cells too.
• Feeding several output columns from one lookup value? Do the MATCH once in a helper cell and point multiple INDEX formulas at it.
• Use XLOOKUP’s fourth argument rather than wrapping the formula in IFERROR. Real errors stay visible that way.
• Keep the lookup formulas and the lookup data on one worksheet.
• Converting a legacy workbook? Change formulas sheet by sheet, recalculating between steps so any regression is easy to localize.

Warnings

• XLOOKUP formulas break with #NAME? in Excel 2019 and older. Check every recipient’s version before standardizing on it.
• MATCH without its third argument defaults to approximate match and returns confidently wrong values on unsorted data.
• Binary and approximate search modes require correctly sorted data. On unsorted data they return invalid results with no error to warn you.
• Benchmark numbers floating around online reflect one machine and one workbook. Excel timing shifts with hardware and version and workbook design, so measure your own file before making a performance decision.
• A single run proves nothing. Microsoft says repeated calculations of the same workbook do not produce identical times, so average several runs.

FAQ

Is XLOOKUP faster than INDEX-MATCH?

Nothing in Microsoft’s documentation supports a meaningful gap for exact-match lookups on unsorted data. Both pay a scan cost proportional to the cells examined before a match appears. The one published figure in this territory concerns VLOOKUP, which Microsoft calls roughly 5 percent faster than MATCH plus INDEX. Pick on compatibility and maintainability instead.

What actually makes a 100,000-row lookup slow?

The search mode, mostly. Exact match on unsorted data scans cell by cell. Sorted data with approximate match behaves like a binary search and barely notices range length. Whole-column references, lookups split across sheets and volatile functions such as OFFSET and INDIRECT pile on top of that.

Does XLOOKUP work in Excel 2019?

No. The Microsoft reference page states XLOOKUP is not available in Excel 2016 and Excel 2019. Those versions display a #NAME? error, so use INDEX-MATCH or VLOOKUP in any file they must open.

How do I time a recalculation properly?

Set calculation to manual, then run the timer macros Microsoft publishes in its calculation performance article. They call Application.CalculateFull behind a high-resolution Windows timer. Ctrl+Alt+F9 does the same forced full calculation from the keyboard if you only need a rough feel.

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

Only when you are touching them anyway, and only if everyone opening the file runs a supported version. Working formulas in a stable file are not a problem to fix, and rewriting hundreds of them adds 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
• Microsoft Learn: Excel performance, tips for optimizing performance obstructions
• Microsoft Learn: Excel performance, improving calculation performance
• Microsoft Learn: Application.CalculateFull method
• Microsoft Learn: Application.Calculate method
• Microsoft Support: Change formula recalculation, iteration, or precision in Excel

📚 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