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.
JosWoolley thanks for your response too, I guess the issue you mentioned based on JoeUser2004's response can be explained and to be able to get the same result. The input range you have is not sorted: {"z";"";"";"";"";"";"";"";"";"a"}, so for using lower or equal search we need to have it sorted in ascending oder, therefore:
=MATCH("Ω",SORT(A:A),1) -> 2which makes sense because after sorted "z" is in the second position and "Ω" is bigger than "z" and sorted lookup_array is as follows: "a", "z","",...,"".
An equivalent result in XMATH is obtained as follows:
=XMATCH("Ω",SORT(M:M),-1,2) -> 2to do a comparison we need to test under the same premise, so using binary search in ascending order (2).
For MATCH approximate search (1) if the lookup_array is not in ascending order unexpected results may be obtained.
Thanks for your input,
David