XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (2023)

we explored itthe basics of xLOOKUP, discovered a wide range ofadvanced use cases for XLOOKUPand the advantages over VLOOKUP and INDEX/MATCH. What we haven't talked about yet is the power of XLOOKUP. Performance is a big concern, especially for larger Excel models with potentially hundreds of thousands of functions to calculate. Let's see how XLOOKUP compares to LOOKUPS and INDEX/MATCH!


  • XLOOKUP great series overview
  • introduction
  • methodology
  • Results
    • Performance of XLOOKUP vs. VLOOKUP
    • Performance of XLOOKUP vs. INDEX/MATCH
    • Binary vs. XLOOKUP normal XLOOKUP compute time
      • What is XLOOKUP's "binary search mode"?
      • So how fast is a binary XLOOKUP compared to a regular XLOOKUP?
      • Binary XLOOKUP performance vs. "fuzzy matching" LOOKUP
    • XLOOKUP 2D "normal" vs. XLOOKUP 2D Binary
    • New: XLOOKUP with "Not found" argument vs. IFERRO(XLOOKUP) vs. IFERRO(VLOOKUP)
  • XLOOKUP performance summary

XLOOKUP great series overview

  • Part 1: Basics of XLOOKUP
  • Part 2: Advanced XLOOKUP: "If not found", "wildcard" and "sorting"
  • Parte 3: 2D-XLOOKUPs
  • Part 4: Let's talk about performance (this article)
  • Part 5: Convert XLOOKUP to VLOOKUP


Since I often work with large Excel models, I'm always concerned about performance. I've already published a book on how to increase the performance of Excel files, and I've written several articles:

  • Quite:Microsoft Excel Acceleration(here it isLink to Amazon page)
  • Study: Excel Performance: Study Shows How to Speed ​​Up Excel by 81%
  • Region settings - big impact on excel calculation speed
  • Speed ​​up Excel in 17 easy steps and calculate faster (+Download)

So, as you can see, I'm very interested in performance issues. Knowing a few basics can really save you a lot of time here. So I took a closer look at the XLOOKUP function. How fast is it compared to VLOOKUP? OR INDEX/PART?


like again byMy previous articlesMy goal is not to have some lab results. Rather, I would like to know how Excel behaves under realistic working conditions.

  • It is measured using an Excel file with 100,000 XLOOKUP functions (and again with the same number of LOOKUP and INDEX/MATCH functions for the same lookup).
  • A VBA macro determines the exact calculation time.
  • The test is done on a MacBook Air running Windows vis Bootcamp. That's probably the low end of performance which should probably be more realistic than running the latest and fastest PC on steroids... 😉
  • Each adjustment is calculated 20 times. All races were considered.


Performance of XLOOKUP vs. VLOOKUP

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (1)

The most pressing question: how fast is XLOOKUP vs. VLOOKUP? Is XLOOKUP really an alternative to VLOOKUP in terms of computational speed?

Surprisingly,XLOOKUP takes 40% longer to calculate than VLOOKUPin our example file.

So should you avoid XLOOKUP now? Wait, quick... XLOOKUP offers a lot more built-in options, including a "binary" search mode. We'll get to that later.
Also, we've seen a lot of great use cases for XLOOKUP. And considering that the absolute processing time on a low-end computer is still fast: about half a second for 100,000 XLOKUPS is generally not considered bad.

Performance of XLOOKUP vs. INDEX/MATCH

So knowing that XLOOKUP is slower than VLOOKUP is good in itself. But many Excel users (myself included) use INDEX/MATCH for lookups much more often. How does XLOOKUP compare to INDEX/MATCH?

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (2)
(Video) Speed testing INDEX MATCH vs. XLOOKUP - The winner might surprise you 😮

As you can see in the graph on the right, the computation time for INDEX/MATCH is about the same as for VLOOKUP. As the computation times for VLOOKUP and INDEX/MATCH are at similar levels, the performance of XLOOKUP compared to INDEX/MATCH is not particularly surprising:XLOOKUP is significantly slower than INDEX/MATCHas well as.

But more than that: Excel also has a new XMATCH function. What about the performance of XLOOKUP vs. INDEX/xFIT?

XLOOKUP is slightly faster than INDEX/XMATCH. This leads to the conclusion:All new "X functions" in Excel are slower than their traditional counterparts.

Binary vs. XLOOKUP normal XLOOKUP compute time

Now it gets really interesting. We know that XLOOKUP is slower than VLOOKUP and INDEX/MATCH. However, the above results only consider "normal" searches and do not use the built-in XLOOKUP binary search mode.

What is XLOOKUP's "binary search mode"?

In a binary search, Excel (or any other program) looks for a value. It relies on sorted data: instead of going through all the items in a list from top to bottom, it splits them in two. If the lookup value is less than the half/median value, the bottom half of the values ​​are halved again, and so on.Wikipedia has more information.about binary searches.

The big advantage: binary searches or generally much faster than "linear" searches.

The downside: the data needs to be sorted.

So how fast is a binary XLOOKUP compared to a regular XLOOKUP?

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (3)

This is impressive: in our exampleWith a binary XLOOKUP, 69% of computing time can be saved compared to a normal XLOOKUP. This means that, in absolute times, the test case only took 0.17 seconds to compute 100,000 XLOOKUP functions.

Compared to normal VLOOKUP functions (0.40s on average for 100,000 VLOOKUP; see above), it still saves more than half the time.

(Video) Which is faster XLOOKUP or INDEX MATCH?

Binary XLOOKUP performance vs. "fuzzy matching" LOOKUP

As you know, or may have wondered, the VLOOKUP function also has a "high speed" mode. It practically works like this. If you enter "TRUE" as the last argument to VLOOKUP, VLOOKUP will look for a "fuzzy match". So how does a binary XLOOKUP compare to a "roughly matched" VLOOKUP?

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (4)

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


Let's go further: how does 2D XLOOKUP compare to INDEX/MATCH/MATCH? Now we're looking in two directions - see more about 2D searchThis article.

For the following comparison, I used a very simple version of two nested XLOOKUPs (see this article for more information). Also, the function I used for INDEX/MATCH/MATCH was in its simplest form.

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (5)

We saw that INDEX/MATCH is much faster than XLOOKUP. The same seems to be true for INDEX/MATCH/MATCH vs. XLOOKUP 2D.INDEX/MATCH/MATCH calculates approximately 30% faster than 2D XLOOKUPin our test book.

XLOOKUP 2D "normal" vs. XLOOKUP 2D Binary

This is our final test that examines XLOOKUP's performance. How much time can we save using atracks2D XLOOKUP statt normalem 2D XLOOKUP?

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (6)

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

Neu: XLOOKUP with "Not found" argument vs. IFERRO (xPROC) vs. IF.ERRO(vTO SEARCH FOR)

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

Since the XLOOKUP function has a built-in "Not Found" argument, the question is whether it's faster to use it or to wrap a traditional IFERROR function around XLOOKUP (and around VLOOKUP). Here are the results:

(Video) The New XLOOKUP Function: Compared to VLOOKUP & INDEX MATCH in Excel

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (7)

Caption by numbers:

The first three columns (1-3) never returned an error. This means that all values ​​were found.

  1. XLOOKUP function with "If not found" argument; no errors and all values ​​found.
  2. IFERROR function wrapped around the XLOOKUP function; found all values ​​again (no error).
  3. Same as number two, but with the VLOOKUP function instead of the XLOOKUP function.

The first two options (XLOOKUP with "If not found" argument vs. IFERROR around XLOOKUP) require similar compute times (~6% difference, but probably not statistically significant due to test setup). But using the "traditional" IFERROR around the VLOOKUP function is significantly faster.

The next three columns (4-6) compare whether all functions return #N/AError.

  1. XLOOKUP function with "If not found" argument; All functions return #N/A errors and therefore use 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 attached IFERROR function.
  3. Same as number two, but with the VLOOKUP function instead of the XLOOKUP function.

Also in this case (if all XLOOKUP and LOOKUP result in #N/A errors), the two versions of XLOOKUP (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 be significantly shorter with the IFERROR/VLOOKUP combination.

To wantincrease your productivityin excel format?

Get the Excel Ribbon for Teachers!

XLOOKUP Performance: How fast is the new XLOOKUP compared to VLOOKUP? (8)

To know more

(Video) Speed Test - VLOOKUP, XLOOKUP, INDEX MATCH, Binary Search in Excel

Download the free trial

to add120+ great featuresto stand out!

XLOOKUP performance summary

XLOOKUP is much more powerful than VLOOKUP or INDEX/MATCH. It offers a wide range of built-in options. But when it comes to performance, XLOOKUP is significantly slower than its "old" competitors VLOOKUP and INDEX/MATCH. Binary search mode definitely helps here. The results for a bidirectional search are also similar: a "normal" 2D XLOOKUP is slower than INDEX/MATCH/MATCH, but a binary 2D XLOOKUP can save a lot of time.

Anterior: 2D XLOOKUP

(Video) How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

Next: Convert XLOOKUP to VLOOKUP


How much faster is Xlookup? ›

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).

What is the fastest lookup function in Excel? ›

INDEX-MATCH in Two Formulas, Sorted Data

In other words, using the two-formula INDEX-MATCH approach against sorted data can be significantly faster than using either VLOOKUP or the one-formula INDEX-MATCH technique, and is best practice.

Is Xlookup newer than VLOOKUP? ›

Excel's XLOOKUP and VLOOKUP functions are used to analyze large sets of data quickly. VLOOKUP is a function that has been used in Excel for years. However, there is a newer function that you should be using called XLOOKUP. Both have their advantages and limitations.

What are the disadvantages of Xlookup? ›

Cons or Drawbacks

There are also a few potential issues to be aware of. Additional [optional] arguments can make the function look overwhelming to new users. Returns a #VALUE! error if the lookup and return arrays are not the same length.

What are the limitations of Xlookup? ›

Does Xlookup have a limit? The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.

Is there a faster function than VLOOKUP in Excel? ›

INDEX-MATCH is much more flexible than Excel's “lookup” functions. 2. At its worst, INDEX-MATCH is slightly faster than VLOOKUP; at its best, INDEX-MATCH is many-times faster.

What is faster than VLOOKUP? ›

INDEX-MATCH is much better:
  • It's never slower than VLOOKUP and can be much faster.
  • It returns a reference rather than a value, which allows us to use it for more purposes.
  • It doesn't care where the result array is with regard to the lookup array.
  • It can return approximate matches from data sorted largest to smallest.

What's more advanced than VLOOKUP? ›

Simply put, the LOOKUP Function is better than VLOOKUP, as it's less restrictive in its use. It was only introduced by Microsoft in 2016, so it's still new to most users. Benefits of LOOKUP vs VLOOKUP: Users can search for data both vertically (columns) and horizontally (rows)

Is Xlookup slower than VLOOKUP? ›

XLOOKUP has many amazing capabilities but is slower than VLOOKUP and INDEX MATCH on huge data sets. Hopefully, Microsoft will improve its performance in the future versions. OFFSET MATCH is the fastest in Excel 32-bit.

What is the advantage of Xlookup over VLOOKUP? ›

XLOOKUP searches for data both horizontally and vertically. VLOOKUP searches only vertically. XLOOKUP can refer to the left of the lookup_value. It always looks up the values towards the right of the lookup_value.

Why did Xlookup stop working? ›

error in your XLOOKUP function, the most likely reason is that your lookup array and your return array are not the same size. In my example, the reason the arrays are different sizes is that there is a blank cell at the end of my return array.

Which is faster INDEX match or Xlookup? ›


We have seen that INDEX/MATCH is a lot quicker than XLOOKUP.

What is the advantage of Xlookup? ›

XLOOKUP benefits
  • XLOOKUP can lookup data to the right or left of lookup values.
  • XLOOKUP can return multiple results (example #3 above)
  • XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
  • XLOOKUP can work with vertical and horizontal data.
  • XLOOKUP can perform a reverse search (last to first)

How much faster is INDEX match than VLOOKUP? ›

With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP. Additionally, however, you can use a version of the INDEX-MATCH technique to calculate MUCH more quickly than with VLOOKUP.

What is better than Xlookup? ›

Vlookup is easier to grasp and often all you really need. Index/Match can search right-to-left or left-to-right and doesn't require you select as large an array in most cases. No matter what side of the fence you're on with that debate, XLOOKUP seems to have outdone them BOTH.


1. XLOOKUP vs Lookup vs Index Match? Xlookup performance issues
(Profectus Academy)
2. XLOOKUP - 5 real examples that will show you why it’s better to use it instead of VLOOKUP
(Tech Tutorials)
3. XLOOKUP for Excel: Explained in 3 Minutes
(Excel Campus - Jon)
4. Compare VLOOKUP XLOOKUP INDEX and DGET Functions- Same Work Situation
5. Stop Using VLOOKUP Use XLOOKUP for Faster Results
(Creative Exceller)
(Excel in a Minute)


Top Articles
Latest Posts
Article information

Author: Sen. Ignacio Ratke

Last Updated: 23/07/2023

Views: 6073

Rating: 4.6 / 5 (76 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Sen. Ignacio Ratke

Birthday: 1999-05-27

Address: Apt. 171 8116 Bailey Via, Roberthaven, GA 58289

Phone: +2585395768220

Job: Lead Liaison

Hobby: Lockpicking, LARPing, Lego building, Lapidary, Macrame, Book restoration, Bodybuilding

Introduction: My name is Sen. Ignacio Ratke, I am a adventurous, zealous, outstanding, agreeable, precious, excited, gifted person who loves writing and wants to share my knowledge and understanding with you.