Excel XLOOKUP vs INDEX-MATCH: Speed Test on 100k Rows
Written by Alex Rivera, Editor · Last updated July 21, 2026 · Fact-checked based on public sources as of this date
At 100,000+ rows, INDEX/MATCH with bounded ranges tends to outperform XLOOKUP in mixed independent tests, especially with multiple criteria — but XLOOKUP with search_mode set for sorted data can close or reverse that gap. Bounded ranges (A2:A50000, not A:A) matter more than which function you pick.
What the benchmarks actually show
Results are more mixed than most “XLOOKUP is faster” headlines suggest. In one independent test at the 100,000-row scale, VLOOKUP actually came out fastest and INDEX/MATCH slowest — the opposite of the common wisdom at smaller scales (10,000 rows), where XLOOKUP won comfortably.
Separately, INDEX/MATCH has been shown to outperform XLOOKUP specifically on datasets above 50,000 rows when the lookup involves multiple criteria — a scenario XLOOKUP handles less natively than a well-built INDEX/MATCH formula.
| Scenario | Faster option | Why |
|---|---|---|
| ~10,000 rows, single lookup | XLOOKUP | Modern function, less overhead at small scale |
| 50,000+ rows, single lookup | Mixed — test your specific case | Results vary by data structure and settings |
| 50,000+ rows, multiple criteria | INDEX/MATCH | More efficient multi-condition handling |
| Sorted data, binary search enabled | XLOOKUP (search_mode=2) | Binary search can be 5-10x faster than VLOOKUP |
When we ran comparable tests ourselves on a 100k-row dataset, the gap between XLOOKUP and INDEX/MATCH was narrow enough that formula choice mattered less than we expected going in — the next section is why.
The one setting that matters more than your formula choice
Across every benchmark we reviewed, one factor showed up consistently as more impactful than XLOOKUP vs. INDEX/MATCH: whether you’re using bounded ranges or whole-column references.
Using A:A instead of A2:A100000 forces Excel to consider well over a million rows even when your actual data ends far sooner — and this measurably slows down all three lookup functions equally. Bounding your ranges to just past your actual data is a free performance win regardless of which formula you pick.
Which one should you actually use
Based on the combined evidence: if you’re on Excel 365 or 2021 and your data is sorted, XLOOKUP with search_mode=2 is the more future-proof choice and handles edge cases (like left-lookups) more cleanly than INDEX/MATCH. If you need compatibility with Excel 2019 or earlier, or you’re doing multi-criteria lookups on very large datasets, INDEX/MATCH with bounded ranges is the safer bet.
In practice, the performance difference between the two at realistic dataset sizes is smaller than the difference bounded ranges make — so if you’re optimizing a slow spreadsheet, check your range references before you rewrite your lookup formulas.
- Bound your ranges — this matters more than formula choice
- Use XLOOKUP with search_mode=2 for sorted data on modern Excel
- Use INDEX/MATCH for multi-criteria lookups on very large datasets
- Test your specific file — results vary by data structure
A quick way to test this on your own file
Rather than trusting any benchmark blindly, test on your actual data: wrap your lookup formula in a helper cell using NOW() before and after a forced recalculation (Ctrl+Alt+F9), or more simply, just watch Excel’s status bar calculation indicator when you switch between formula versions on a large sheet. The difference is often visible without formal timing on datasets north of 100,000 rows.
If your workbook feels sluggish and you’re not sure which formula is the bottleneck, temporarily convert one lookup column to hard values and see if performance improves — that isolates whether the lookup itself is the drag, or something else in the sheet (volatile functions, excessive conditional formatting, and array formulas are common co-conspirators).
FAQ
Q: Is VLOOKUP ever still the right choice?
Rarely for new work — it’s generally the slowest of the three at scale and lacks XLOOKUP’s flexibility, but it remains relevant for compatibility with older files.
Q: Does this apply to Google Sheets too?
Sheets has its own performance characteristics and doesn’t support XLOOKUP identically — treat these benchmarks as Excel-specific.
Q: What about Power Query instead of lookup formulas entirely?
For very large, repeatedly-refreshed datasets, Power Query merges often outperform any in-cell lookup formula — worth considering if you’re rebuilding a report from scratch rather than patching an existing one.
Q: Does calculation mode (automatic vs manual) affect this comparison?
It affects perceived speed dramatically — on very large sheets, switching to manual calculation and recalculating only when needed can make any formula choice feel instant, independent of which lookup function you’re using.
If spreadsheet performance is part of a bigger productivity audit, it’s worth checking whether other Office apps are also acting sluggish on the same machine before assuming Excel is the only culprit.
Windows 11 24H2 Wi-Fi Keeps Disconnecting: 5 Fixes
