Forum Discussion
XMATCH and MATCH return different result for approximate search lookup_array with repetitive values
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#)