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 old MATCH does not have. That is, the the latter can be employed to perform a binary search in order to determine the position of the last value in a range (useful, for example, for defining dynamic ranges when one is not using a Table).
For example, with A1 and A10 containing "z" and "a" respectively (and all other cells in that column blank):
=MATCH("Ω",A:A)
which is equivalent to
=MATCH("Ω",A:A,1))
will return 10, as desired.
(Of course, one can replace "Ω" in the above with a suitably large number for ranges comprising numerics only.)
However, I cannot find a way to replicate this functionality using XMATCH. As a result, I must continue to use MATCH in such cases.
I believe that the task of determining the last-used cell in a column using worksheet formulas is common enough that this post will be of interest to many of those who read it. Alternative formulas for obtaining that end result are linear, and therefore potentially thousands of times slower:
=MAX(IF(A:A<>"",ROW(A:A)))
for example, is a horribly inefficient construction, having to interrogate more than a million cells.
Am I missing something, or is this a huge oversight in the workings of the XMATCH function? Wasn't this function intended to be the 'successor' to MATCH, for which I would read 'able to mimic all that MATCH can do, plus extras'?
Regards
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.
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
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)?
