Forum Discussion
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...
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
JosWoolley
Yes, you right, I overestimated TAKE(). On the range without blanks inside I usually use
=XMATCH(,A:A)-1
I had the same idea but no time to check perf. against MATCH this morning
Just did it with 50K rows x 500 columns. From what I can visually observe MATCH still perform better (here)
Yes, MATCH has better performance, XMATCH has more options. What to use depends on concrete situation.
