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!
Satisfied
- 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
- 2D XLOOKUP vs. INDEX/CORRESP/CORRESP
- 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
introduction
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?
methodology
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.
Results
Performance of XLOOKUP vs. VLOOKUP
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?
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?
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.
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?
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.
2D XLOOKUP vs. INDEX/CORRESP/CORRESP
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.
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?
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:
Caption by numbers:
The first three columns (1-3) never returned an error. This means that all values were found.
- XLOOKUP function with "If not found" argument; no errors and all values found.
- IFERROR function wrapped around the XLOOKUP function; found all values again (no error).
- 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.
- XLOOKUP function with "If not found" argument; All functions return #N/A errors and therefore use the If not found option.
- 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.
- 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!
To know more
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
Next: Convert XLOOKUP to VLOOKUP
FAQs
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.
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.
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? ›- 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.
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? ›XLOOKUP versus INDEX and MATCH
We have seen that INDEX/MATCH is a lot quicker than XLOOKUP.
- 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)
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.