So you find yourself using a spreadsheet with a million VLOOKUP functions and you wonder: can it go faster?
When I encountered a slow spreadsheet in the wild, I wondered the same thing. After a quick Google search, I found heaps of articles claiming that INDEX MATCH would be a faster replacement for VLOOKUP. Today I’ll show you the effect on calculation time based on a benchmark of 1 million lookups.
I use 3 solutions to sift through 10,000 rows of data to retrieve 10 different columns 100,000 times:
- VLOOKUP: the standard function using an exact match
- INDEX MATCH: replacing the VLOOKUP function with a standalone INDEX MATCH, so repeated matching for all 10 columns (no caching)
- Optimized INDEX MATCH: returning the matched row number (index) once and then using 10 INDEX functions to look up each row value (caching)
The results were quite surprising to me.
Inexperienced users who just replace VLOOKUP with INDEX MATCH without storing the matched row (the result of the MATCH function) in a separate column, will actually see worse performance. The overhead of using two functions instead of 1 means VLOOKUP is 4% faster in this case.
The real benefit of INDEX MATCH, therefore, lies in its ability to be separated into two calculations. First, we match the row we are looking for, then we retrieve many columns using the index of that row. For a 10 column lookup, INDEX MATCH will outperform VLOOKUP by 19%.
This means that without this ‘caching’ mechanism, or when we just need a single column, sticking with VLOOKUP can be an acceptable strategy.
For now, I have just considered the classical comparison between VLOOKUP and INDEX MATCH. I plan to expand these benchmarks in the future and have found some interesting strategies online. Microsoft famously introduced XLOOKUP. But I’ve also seen reports of people using double VLOOKUPs to optimize binary search in Excel.