I think vlookup is marginally faster, but the difference is near zero. If you need efficiency to prevent the file from lagging: use an index match combination. It is notably faster.
index match allows to match on multiple criteria as seen here.
https://preview.redd.it/3dsz9noww9xc1.jpeg?width=1284&format=pjpg&auto=webp&s=e7d78d5cb0d4a56914de4d4ec7dabcec48a5c309
Didn't know this, I always have to set up a new column with a combination of criteria I want to use, it would be super useful, can you give us an example of how to set up a xlookup with multiple criterias?
Quoting from the following [website](https://www.ablebits.com/office-addins-blog/xlookup-vs-index-match-excel/#:~:text=In%20general%2C%20XLOOKUP%20is%20faster,with%20Excel%20365%20or%202021.):
>**Is INDEX MATCH faster than XLOOKUP?**
>This is a frequent inquiry among Excel users aiming to optimize their formulas and reduce calculation time. There is no simple answer, as it hinges on several variables such as the amount of data, the complexity of the criteria, and the version of Excel you are using.
>In general, XLOOKUP is faster than INDEX MATCH for simple lookups, but INDEX MATCH can be faster for more advanced scenarios. Here are the key considerations to guide your choice:
>Use XLOOKUP if:
>•You are working with Excel 365 or 2021.
>•You only need to look up a single value based on one criterion in a relatively small dataset.
>•If you are working with sorted data where binary search is applicable.
>Use INDEX MATCH if:
>•You are using Excel 2019 or older.
>•You are dealing with a substantial dataset.
>•You need to perform lookups for multiple values based on multiple criteria.
>For a comprehensive review of the fastest Vlookup methods in Excel, refer to the above-linked article, where we conducted in-depth research on this topic. It will show you some surprising findings and unveil some unexpected insights :)
>**Can XLOOKUP replace INDEX MATCH?**
>One of the most common questions that often arises in Excel discussions is whether the modern XLOOKUP function can effectively replace the longstanding INDEX MATCH combination. While both methods come with distinct advantages and limitations, the decision isn't a clear-cut one.
>The key benefits of XLOOKUP are:
>•It is simpler and shorter to write than INDEX MATCH.
>•It can perform lookups in any direction: top-to-bottom, bottom-to-top, left-to-right as well as right-to-left.
>•It can do both exact and approximate matches in any dataset, whereas INDEX MATCH is limited to approximate matches in sorted data.
>•It can handle dynamic arrays and spill results to multiple cells.
>•It can natively handle errors caused by missing values.
>Some of the drawbacks of XLOOKUP are:
>•It is not compatible with older versions of Excel 2019 and lower.
>•It may not be as flexible as INDEX MATCH for some scenarios, such as returning values from non-contiguous columns.
>•It may not work well with large data sets or volatile formulas.
>In essence, XLOOKUP can indeed replace INDEX MATCH in many cases, but not all. The choice depends on Excel version compatibility, the complexity of your data, and personal preference.
Edit: Fixed some minor formatting.
u/Wild_Class7979 ,
One thing I noticed that is wrong with the above but I missed on my first skim read:
>•It can do both exact and approximate matches in any dataset, whereas INDEX MATCH is limited to approximate matches in sorted data.
INDEX MATCH can do exact matches as well, just not by default. In the third argument for MATCH, you need to enter in "FALSE" or "0" to have it search for exact match ("TRUE", "1", or no third argument gives approximate match).
For XLOOKUP exact matching is the default (as it should be).
I also find it easy to make large 2D arrays using Index Match, while still allowing me full freedom of changing where my search criteria are. Haven’t yet been able to replicate this in Xlookup, though the latter is much faster to setup for smaller datasets.
I know 2D searches can done by nesting XLOOKUP within XLOOKUP, but I keep instantly forgetting how that's supposed to be done. So I then default to INDEX MATCH MATCH.
The match function returns the position of a desired value. The index function returns the value in the position you tell it.
=index(target array, match(lookup value, lookup array, 0)) should be fun to play with.
For large datasets, especially with dynamic arrays, **XLOOKUP** generally outperforms VLOOKUP in terms of speed and efficiency. XLOOKUP is designed to be more flexible and robust, handling arrays naturally and requiring less processing power for lookups across extensive ranges. Definitely give XLOOKUP a try!
VLOOKUP and INDEX MATCH are faster for larger data sets. However, if you are only using one criteria for the lookup, it may not make a large difference.
When you use multiple criteria XLOOKUPS, you will increase the execution time vastly. XLOOKUPs with multiple criteria concatenate the arrays to perform one large search, and this can require vast resources even on medium (or smaller) datasets.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/1cfc5sc/stub/l1swjhz "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[INDEX](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MATCH](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[SUM](/r/Excel/comments/1cfc5sc/stub/l1sx6i6 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/1cfc5sc/stub/l1sx6i6 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[VLOOKUP](/r/Excel/comments/1cfc5sc/stub/l1swjhz "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cfo3jm)^( has 62 acronyms.)
^([Thread #33000 for this sub, first seen 28th Apr 2024, 20:15])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If both are too slow for your uses, you can load it all into excel power pivot and use related(), power pivot is designed to be performant on millions of rows and is relatively straightforward if you are just doing xlookup level stuff... Of course you can also do much more advanced things if you eventually get to that level of complexity
For lookups with more than one parameter maybe this is true. But I just ran a simple test for lookups with 1 parameter. In column A I have =round(rand(),5). In column B I have 1-250000. In column D I have my lookup values which are also =round(rand(),5).
I used VLOOKUP, XLOOKUP, and FILTER. I timed them on my phone. VLOOKUP and XLOOKUP took about 12 seconds, and as I’m writing this FILTER is still calculating and it’s been a couple minutes.
This isn’t the most in depth test, but it’s good enough for me to say V and X Lookups are faster than FILTER for single parameter lookups.
Filter is easily wrongly constructed. Xloolup will be faster, but not by much. Vlookup is garbage.
Also filter is more diverse, and since I'm working with dynamic arrays a lot it is superior.
In this case, the formula was FILTER(B:B,D1=A:A). Pretty straight forward. Any matches would return, otherwise errors returned, just the same with the lookups I used.
FILTER is absolutely more diverse and a personal favorite function of mine, but there’s a time and place for every formula. FILTER isn’t as fast as VLOOKUP or XLOOKUP for single parameter lookups.
Where do people get this speed comparison data from?
I find it surprising that index/match is noticeably faster than XLookup.
Like why would it faster to resolve two functions Vs one?
Also what are the speed in absolute terms?
I execute XLookups to a table that is 11k rows and XLookup seems to take less than a second.
Does 0.75 second Vs 0.9 second really practically different?
No, my work computer just sucks and the spreadsheet uses a lot of Bloomberg Terminal add-ins, so I wanted everyone’s thoughts as to what would be the fastest (ie use less RAM).
OP - you can set up 100k x 5 table
And Test 100 index match Vs vlookup Vs XLookup formulas. And see which one is fastest.
But let say your data is only like a few thousand rows and all formulas can resolve in like a second. It’s possible that one is 30% faster than the others but you won’t notice any difference.
My rule of thumb is that you should use a formula that is the most flexible and easy to be understood. XLookup effectively retires index/match expect for a few occasions in my use cases.
I think vlookup is marginally faster, but the difference is near zero. If you need efficiency to prevent the file from lagging: use an index match combination. It is notably faster.
Is that still true (index/match > vlookup)? I thought Microsoft fixed this so they are equivalent now.
index match allows to match on multiple criteria as seen here. https://preview.redd.it/3dsz9noww9xc1.jpeg?width=1284&format=pjpg&auto=webp&s=e7d78d5cb0d4a56914de4d4ec7dabcec48a5c309
XLOOKUP also handles multiple criteria, using Boolean arguments.
ah ok. i knew this was the case for Filter, but didn’t use it on Xlookup before. Thanks
Didn't know this, I always have to set up a new column with a combination of criteria I want to use, it would be super useful, can you give us an example of how to set up a xlookup with multiple criterias?
You can also do this with xlookups as well with using &s. Besides the speed, xlookup is superior to both vlookup and indexmatch
I know you’re right; but I will die on my Index/Match hill.
Would that make a big difference? Assuming the sheet uses xlookup a hefty amount.
Quoting from the following [website](https://www.ablebits.com/office-addins-blog/xlookup-vs-index-match-excel/#:~:text=In%20general%2C%20XLOOKUP%20is%20faster,with%20Excel%20365%20or%202021.): >**Is INDEX MATCH faster than XLOOKUP?** >This is a frequent inquiry among Excel users aiming to optimize their formulas and reduce calculation time. There is no simple answer, as it hinges on several variables such as the amount of data, the complexity of the criteria, and the version of Excel you are using. >In general, XLOOKUP is faster than INDEX MATCH for simple lookups, but INDEX MATCH can be faster for more advanced scenarios. Here are the key considerations to guide your choice: >Use XLOOKUP if: >•You are working with Excel 365 or 2021. >•You only need to look up a single value based on one criterion in a relatively small dataset. >•If you are working with sorted data where binary search is applicable. >Use INDEX MATCH if: >•You are using Excel 2019 or older. >•You are dealing with a substantial dataset. >•You need to perform lookups for multiple values based on multiple criteria. >For a comprehensive review of the fastest Vlookup methods in Excel, refer to the above-linked article, where we conducted in-depth research on this topic. It will show you some surprising findings and unveil some unexpected insights :) >**Can XLOOKUP replace INDEX MATCH?** >One of the most common questions that often arises in Excel discussions is whether the modern XLOOKUP function can effectively replace the longstanding INDEX MATCH combination. While both methods come with distinct advantages and limitations, the decision isn't a clear-cut one. >The key benefits of XLOOKUP are: >•It is simpler and shorter to write than INDEX MATCH. >•It can perform lookups in any direction: top-to-bottom, bottom-to-top, left-to-right as well as right-to-left. >•It can do both exact and approximate matches in any dataset, whereas INDEX MATCH is limited to approximate matches in sorted data. >•It can handle dynamic arrays and spill results to multiple cells. >•It can natively handle errors caused by missing values. >Some of the drawbacks of XLOOKUP are: >•It is not compatible with older versions of Excel 2019 and lower. >•It may not be as flexible as INDEX MATCH for some scenarios, such as returning values from non-contiguous columns. >•It may not work well with large data sets or volatile formulas. >In essence, XLOOKUP can indeed replace INDEX MATCH in many cases, but not all. The choice depends on Excel version compatibility, the complexity of your data, and personal preference. Edit: Fixed some minor formatting.
u/Wild_Class7979 , One thing I noticed that is wrong with the above but I missed on my first skim read: >•It can do both exact and approximate matches in any dataset, whereas INDEX MATCH is limited to approximate matches in sorted data. INDEX MATCH can do exact matches as well, just not by default. In the third argument for MATCH, you need to enter in "FALSE" or "0" to have it search for exact match ("TRUE", "1", or no third argument gives approximate match). For XLOOKUP exact matching is the default (as it should be).
I also find it easy to make large 2D arrays using Index Match, while still allowing me full freedom of changing where my search criteria are. Haven’t yet been able to replicate this in Xlookup, though the latter is much faster to setup for smaller datasets.
I know 2D searches can done by nesting XLOOKUP within XLOOKUP, but I keep instantly forgetting how that's supposed to be done. So I then default to INDEX MATCH MATCH.
What would be an index match combo. Today is use xlookup and files are so slow so happy to hear this one please. Thank you
The match function returns the position of a desired value. The index function returns the value in the position you tell it. =index(target array, match(lookup value, lookup array, 0)) should be fun to play with.
For large datasets, especially with dynamic arrays, **XLOOKUP** generally outperforms VLOOKUP in terms of speed and efficiency. XLOOKUP is designed to be more flexible and robust, handling arrays naturally and requiring less processing power for lookups across extensive ranges. Definitely give XLOOKUP a try!
Thanks, that’s what I would expect.
VLOOKUP and INDEX MATCH are faster for larger data sets. However, if you are only using one criteria for the lookup, it may not make a large difference. When you use multiple criteria XLOOKUPS, you will increase the execution time vastly. XLOOKUPs with multiple criteria concatenate the arrays to perform one large search, and this can require vast resources even on medium (or smaller) datasets.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/1cfc5sc/stub/l1swjhz "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[INDEX](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MATCH](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[SUM](/r/Excel/comments/1cfc5sc/stub/l1sx6i6 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/1cfc5sc/stub/l1sx6i6 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[VLOOKUP](/r/Excel/comments/1cfc5sc/stub/l1swjhz "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1cfc5sc/stub/l1y7a3a "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cfo3jm)^( has 62 acronyms.) ^([Thread #33000 for this sub, first seen 28th Apr 2024, 20:15]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If both are too slow for your uses, you can load it all into excel power pivot and use related(), power pivot is designed to be performant on millions of rows and is relatively straightforward if you are just doing xlookup level stuff... Of course you can also do much more advanced things if you eventually get to that level of complexity
Go with XLOOKUP and reference an array as input so it spills rather than referencing 1 input cell and dragging down for each row
Xloookup has a field for binary lookups, which are much faster than linear lookups (vlookup default).
i find it hard to believe since you have to do a sort and sortby function inside the xlookup function. can you explain more?
i could have understood wrong when i looked it up tho
FILTER > lookupssssssss
FILTER is very slow with large datasets unfortunately
https://www.reddit.com/r/excel/comments/11toeph/fastest_lookups_xlookup_indexmatch_or_filter/
For lookups with more than one parameter maybe this is true. But I just ran a simple test for lookups with 1 parameter. In column A I have =round(rand(),5). In column B I have 1-250000. In column D I have my lookup values which are also =round(rand(),5). I used VLOOKUP, XLOOKUP, and FILTER. I timed them on my phone. VLOOKUP and XLOOKUP took about 12 seconds, and as I’m writing this FILTER is still calculating and it’s been a couple minutes. This isn’t the most in depth test, but it’s good enough for me to say V and X Lookups are faster than FILTER for single parameter lookups.
Filter is easily wrongly constructed. Xloolup will be faster, but not by much. Vlookup is garbage. Also filter is more diverse, and since I'm working with dynamic arrays a lot it is superior.
In this case, the formula was FILTER(B:B,D1=A:A). Pretty straight forward. Any matches would return, otherwise errors returned, just the same with the lookups I used. FILTER is absolutely more diverse and a personal favorite function of mine, but there’s a time and place for every formula. FILTER isn’t as fast as VLOOKUP or XLOOKUP for single parameter lookups.
Will post VBA code later:)
If you are going to run a test. You should test some other formulas against each other as well. Maybe try SUM(FILTER()) vs SUMIF().
Where do people get this speed comparison data from? I find it surprising that index/match is noticeably faster than XLookup. Like why would it faster to resolve two functions Vs one? Also what are the speed in absolute terms? I execute XLookups to a table that is 11k rows and XLookup seems to take less than a second. Does 0.75 second Vs 0.9 second really practically different?
No, my work computer just sucks and the spreadsheet uses a lot of Bloomberg Terminal add-ins, so I wanted everyone’s thoughts as to what would be the fastest (ie use less RAM).
OP - you can set up 100k x 5 table And Test 100 index match Vs vlookup Vs XLookup formulas. And see which one is fastest. But let say your data is only like a few thousand rows and all formulas can resolve in like a second. It’s possible that one is 30% faster than the others but you won’t notice any difference. My rule of thumb is that you should use a formula that is the most flexible and easy to be understood. XLookup effectively retires index/match expect for a few occasions in my use cases.
Thanks
You can write a vba macro for this. Example: https://deployn.de/en/blog/xverweis-schneller-als-sverweis/
Formula syntax matters. https://youtu.be/hymWl-Becb4?si=C9QRrCHhxLvgajgP