For decades, VLOOKUP was the king of Excel functions. It was the first “advanced” formula many of us learned. But in 2019, Microsoft released XLOOKUP, promising to be the “one function to rule them all.”
We know XLOOKUP is more flexible—it defaults to exact match, searches left-to-right and right-to-left, and handles errors gracefully. But is it faster?
If you are analyzing 500,000 rows of sales data, which formula should you use?
The Mechanics: Why VLOOKUP was “Slow”
Legacy VLOOKUP had a reputation for being slow because of how it handles column references. When you select a table array like A:Z, VLOOKUP effectively “loads” that entire block into memory to scan the first column.
If your lookup value is in Column A and your return value is in Column Z, it’s efficient enough. But if your return value is in Column B, VLOOKUP still processes the entire range you selected.
Enter XLOOKUP: Targeted Arrays
XLOOKUP uses two separate arrays:
=XLOOKUP(lookup_value, lookup_array, return_array)
This splits the “Search” column from the “Result” column. Excel’s calculation engine only needs to load those two specific ranges into memory, ignoring the 20 columns of irrelevant data in between.
Winner: XLOOKUP (for memory usage).
The Speed Test: Binary Search
Here is where it gets interesting.
Exact Match (Unsorted Data)
* VLOOKUP(False): Scans row by row until it finds a match. Slow on large data.
* XLOOKUP: Also scans linearly.
* Result: Roughly a tie. XLOOKUP is marginally faster due to the new calculation engine, but not by an order of magnitude.
Approximate Match (Sorted Data)
* VLOOKUP(True): Uses a binary search algorithm. It’s incredibly fast.
* XLOOKUP(…, 2): Also uses binary search.
* Result: Both are blazing fast, capable of searching 1 million rows in milliseconds.
Alternatively: The Double VLOOKUP Trick
Before XLOOKUP, dynamic spreadsheet pros used INDEX + MATCH.
=INDEX(B:B, MATCH(value, A:A, 0))
This was always faster than VLOOKUP because, like XLOOKUP, it only referenced the specific columns needed.
Conclusion: Which should you use?
Use XLOOKUP if:
1. You have Microsoft 365.
2. You want robust formulas that don’t break if you insert a column (VLOOKUP breaks because the column index number is hardcoded).
3. You need to search “Last to First” (finding the latest entry).
Use VLOOKUP if:
1. You are sharing the sheet with users on Excel 2016 or older.
For pure performance on massive datasets, XLOOKUP is the modern champion, not just for speed, but for stability.