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