Site icon Professor Excel

Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?

We’ve explored the basics of XLOOKUP, discovered a wide range of advanced use cases for XLOOKUP and the advantages towards VLOOKUP and INDEX/MATCH. What we have not talked about so far is the performance of XLOOKUP. Especially for larger Excel models with possibly hundreds of thousands of functions to calculate, performance is a major concern. Let’s see how XLOOKUP performs vs. VLOOKUP and INDEX/MATCH!

Overview of the big XLOOKUP series

Introduction

As I am often working with large Excel models, I’m always concerned about performance. I have already published a book about how to increase the performance of Excel files and written several articles:

So, as you can see, I’m pretty interested in the performance topics. Knowing a few basics can really save a lot of time here. That’s why I’ve taken a close look at the XLOOKUP function. How fast is it compared to VLOOKUP? Or INDEX/MATCH?

Methodology

As again for my previous articles, my goal is not to have some laboratory results. I rather want to know, how Excel behaves under realistic work conditions.

Results

Performance of XLOOKUP vs. VLOOKUP

Surprisingly, the performance of XLOOKUP is significantly worse than VLOOKUP.

The most pressing question: How fast is XLOOKUP vs. VLOOKUP? Is XLOOKUP really an alternative for VLOOKUP in terms of calculation speed?

Surprisingly, XLOOKUP needs ~40% longer to calculate than VLOOKUP in our example file.

So, should you avoid XLOOKUP now? Hold on, not that fast… XLOOKUP offers much more built-in options, including a “binary” search mode. We’ll come to that later.
Also, we’ve seen many great use cases for XLOOKUP. And further considering that the absolute calculation time on a low end computer is still fast: roughly half a second for 100,000 XLOOKUPS is usually not considered bad.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Performance of XLOOKUP vs. INDEX/MATCH

So, knowing that XLOOKUP is slower than VLOOKUP is fine by itself. But many Excel users (including me) use INDEX/MATCH much more often for lookups. How is the performance of XLOOKUP vs. INDEX/MATCH?

Performance of XLOOKUP vs. INDEX/MATCH and INDEX/XMATCH.

As you can see in the chart on the right-hand side, the calculation time for INDEX/MATCH is pretty much the same as for VLOOKUP. Because calculation times for VLOOKUP and INDEX/MATCH are on a similar level, the performance of XLOOKUP compared to INDEX/MATCH doesn’t surprise much: XLOOKUP is significantly slower than INDEX/MATCH as well.

But more: Excel also has a new XMATCH function. So, what about the performance of XLOOKUP vs. INDEX/XMATCH?

XLOOKUP is slightly faster than INDEX/XMATCH. That leads to the conclusion: All new “X-functions” in Excel are slower than their traditional counterpart.

Calculation time of Binary XLOOKUP vs. normal XLOOKUP

Now it get’s really interesting. We know that XLOOKUP is slower than VLOOKUP and INDEX/MATCH. But the previous results are only considering “normal” lookups, not utilizing the built-in binary XLOOKUP search mode.

What is the “binary search mode” of XLOOKUP?

A binary search is the way, Excel (or any other program) searches for a value. It relies on sorted data: Instead of looping through all items in a list of table top-down, it cuts it into half. If the search value is smaller then the value at the half / median, it divides the lower half of the values into half again and so on. Wikipedia has more infomation about binary searches.

The big advantage: Binary searches or usually much faster than “linear” searches.

The disadvantage: The data must be sorted.

So, how fast is a binary XLOOKUP vs. a normal XLOOKUP?

That’s impressive: the binary XLOOKUP can save 69% of calculation time compared to a normal XLOOKUP.

That’s impressive: In our example, 69% of the calculation time can be saved using a binary XLOOKUP compared to a normal XLOOKUP. That means in absolute times, the test case just needed 0.17s to calculate 100,000 XLOOKUP functions.

When we compare it to the normal VLOOKUP functions (0.40s on average for 100,000 VLOOKUPs; see above), you still save more than half the time.


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Performance of a binary XLOOKUP vs. VLOOKUP with “approximate match”

As you may know – or have been wondering – also the VLOOKUP function has such “high-speed” mode. It works pretty much the same way. If you put “TRUE” as the last argument of VLOOKUP, VLOOKUP searches for an “approximate match”. So, how does a binary XLOOKUP compare to a “approximate match” VLOOKUP?

Compared to a normal VLOOKUP, the binary XLOOKUP is significantly faster. But a VLOOKUP with a approximate match is still a little bit faster.

The binary XLOOKUP is slightly slower than an approximate VLOOKUP (~16% slower). But compared to both other options (normal XLOOKUP or normal VLOOKUP), the binary XLOOKUP is significantly faster.

2D XLOOKUP vs. INDEX/MATCH/MATCH

Let’s get even more advanced: How does 2D XLOOKUP compare to INDEX/MATCH/MATCH? We now look in two directions – for more information about 2D lookups please refer to this article.

For the following comparison I’ve used a fairly simple version of two XLOOKUP nested into each other (please refer to this article for more information). Also, for INDEX/MATCH/MATCH, the function I’ve used was in it’s simplest form.

The performance of a 2D XLOOKUP is worse than INDEX/MATCH/MATCH in terms of calculation performance.

We’ve seen that INDEX/MATCH is much faster than XLOOKUP. The same seems to be true for INDEX/MATCH/MATCH in comparison with a 2D XLOOKUP. INDEX/MATCH/MATCH calculates around 30% faster than a 2D XLOOKUP in our test workbook.

“Normal” 2D XLOOKUP vs. Binary 2D XLOOKUP

This is our last test examining the performance of XLOOKUP. How much time can we save if we use a binary 2D XLOOKUP instead of a normal 2D XLOOKUP?

But again, the performance of a binary 2D XLOOKUP is better than a “normal” 2D XLOOKUP and INDEX/MATCH/MATCH.

As expected, a 2D XLOOKUP with a binary search mode is much faster than a normal 2D XLOOKUP. It also needs 18% less time to calculate than an INDEX/MATCH/MATCH.

New: XLOOKUP with “Not Found” argument vs. IFERROR(XLOOKUP) vs. IFERROR(VLOOKUP)

This comparison was inspired by the comments below (thanks, Øystein and Johan!).

Because the XLOOKUP function has a built-in “Not Found” argument, the question is if it’s faster to use this or wrap a traditional IFERROR function around XLOOKUP (and around VLOOKUP). Here are the results:

Legend by numbers:

The first three columns (1-3) never returned an error. That means, all values were found.

  1. XLOOKUP function using the “If Not Found” argument; no error and all values were found.
  2. IFERROR function wrapped around the XLOOKUP function; again, all values found (no error).
  3. Same as number two but using the VLOOKUP instead of the XLOOKUP function.

The first two options (XLOOKUP with “If Not Found” argument vs. IFERROR around XLOOKUP) need similar calculation times (~6% difference but due to the test setup probably statistically not significant). But to using the “traditional” IFERROR around the VLOOKUP function is significantly faster.

The next three columns (4-6) compare if all function return #N/A errors.

  1. XLOOKUP function using the “If Not Found” argument; all functions return #N/A errors and therefore utilize the “If Not Found” option.
  2. IFERROR function wrapped around the XLOOKUP function; all functions return #N/A errors and therefore jump to the second argument of the enclosing IFERROR function.
  3. Same as number two but using the VLOOKUP instead of the XLOOKUP function.

Also in this case (when all XLOOKUPs and VLOOKUPs would lead to #N/A errors), the two XLOOKUP versions (numbers 4 and 5) have similar results. But when it comes to the traditional IFERROR with the VLOOKUP function, the difference is quite big: calculation times seem to significantly lower with the IFERROR / VLOOKUP combination.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Summary of the performance of XLOOKUP

XLOOKUP is much more powerful than VLOOKUP or INDEX/MATCH. It offers a great range of options right built-in. But when it comes to performance, XLOOKUP is significantly slower than it’s “old” competitors VLOOKUP and INDEX/MATCH. The binary search mode definitely helps here. Also, the results for a 2-way-lookup are similar: a “normal” 2D XLOOKUP is slower than INDEX/MATCH/MATCH, but a binary 2D XLOOKUP can save a lot of time.

Exit mobile version