VLOOKUP vs INDEX MATCH in 2022 - It's not that bad

VLOOKUP vs INDEX MATCH in 2022 - It's not that bad

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:

  1. VLOOKUP: the standard function using an exact match
  2. INDEX MATCH: replacing the VLOOKUP function with a standalone INDEX MATCH, so repeated matching for all 10 columns (no caching)
  3. 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.

A bar chart showing the difference in performance (time in seconds) between the three different solutions. With optimized INDEX MATCH on top (lowest) beating out VLOOKUP by 19%. VLOOKUP itself is 4% faster than unoptimized INDEX MATCH (bottom).

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.

Monday Morning Mashup

Every Monday morning I will send you an email with things I discovered during the week that I found helpful. Ranging from interesting theories to practical Excel tips and insightful articles I read, this newsletter will make your Monday a little bit better for sure!