Forum Discussion
JosWoolley
Mar 30, 2023Iron Contributor
MATCH vs XMATCH: Binary Search to Find the Last Entry within a Range
I originally posted this as a response to this post, though thought it deserved a post in its own right. To reiterate: I believe that the new XMATCH function has one serious shortcoming which the...
Lorenzo
Mar 30, 2023Silver Contributor
- JosWoolleyMar 30, 2023Iron Contributor
Ah, wait. I forgot that a search_mode parameter of -1 for XMATCH is a linear search, not binary, so that will most likely be much slower than the MATCH construction I gave.
- LorenzoMar 31, 2023Silver Contributor
Ah, wait. I forgot that a search_mode parameter of -1 for XMATCH is a linear search, not binary, so that will most likely be much slower than the MATCH construction I gave.
I don't have the tools to measure this precisely but on a Sheet with 50k rows * 500 columns full of chars I could observe that repeating 500 times =MATCH("Ω", Sheet!A:A) appears to calc. instantaneously. On the other hand =XMATCH("*", Sheet!A:A, 2, -1) takes a bit more than 3 seconds here
- JosWoolleyMar 30, 2023Iron Contributor
Nice work! That will work for text entries. But is there an equivalent for a range comprising numerics, equivalent to
=MATCH(99^99,A:A)
?
- SergeiBaklanMar 30, 2023MVP
- JosWoolleyMar 30, 2023Iron Contributor
Thanks, Sergei. But not very fast, I'm afraid. With A1:A1048575 containing 999 and A1048576 containing 99, 500 copies of your formula take considerably more time to calculate than 500 copies of the formula =MATCH(88^88,A:A). In the latter case, the 500 formulas calculate instantaneously; in your case, several seconds.
This makes sense, since, again, you are not employing binary searching.
Also, 3 functions instead of 1 seems unnecessary overkill.
Regards