Forum Discussion
XMATCH and MATCH return different result for approximate search lookup_array with repetitive values
Interesting. I also investigated XMATCH some time back and was dismayed to discover that there was no way to replicate one of the most important features of the old MATCH function (which I continue to use for this very reason). That feature relates to the ability to use fast binary searching in order to determine the position of the last value in a range (useful 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)
(or, equivalently =MATCH("Ω",A:A,1))
will return 10, as desired.
However, there is no way to obtain this result using XMATCH, as far as I can tell.
A huge oversight in the working of XMATCH, in my opinion.
For me
= XMATCH("Ω", A:A, -1)worked well (though it is many years since I have used a whole column reference; I am usually content to take the end of a Table to define a column dynamically and the problem does not arise for array formulas and their spilt ranges).
My recollection is that there is little need for the ±2 parameter in XLOOKUP and XMATCH since the functions are designed to exploit binary search methods and will perform sorting in memory to ensure that apparently linear searches are performed efficiently.
It returns position of next smaller within the range, not the position of last non-blank, isn't it?
True, I allowed myself to get mesmerised by the repeated values as in
{3,2,2}Not that they are techniques I have needed for many years now, I am far more likely to use formulas more tightly tied to the specifics of the problem, such as
= ROWS(Table1) =COUNT(squares#)
