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
Actually, David, I think you've misunderstood the point. Even when the lookup_array is not sorted, providing the choice of lookup_value is chosen to be greater than any value within lookup_array we can exploit this to return the last non-blank entry in a one-dimensional range.
Regards
JosWoolley I didn't know what was your intention with the formula, I was trying to explain how you can get the same result following the specifications, but not getting the expected result you are looking for. Now from your post, I see you are looking for the position of the last non-blank cell.
Maybe it is a side effect or a glitch in MATCH, because we are not using it according to its specification. For example:
MATCH(4,{3,2,1}) -> 3You can permute the array in any order and you get the same result, i.e. the last position. Which is the correct position when we invoke the function according to its specification (ascending order):
MATCH(4,{1,2,3}) -> 3On contrary XMATH produces the correct result:
XMATCH(4,{3,2,1},-1) -> 1with the above parameters, there is no need to have the array sorted.
So maybe rather than a wrong XMATCH behavior is a glitch in MATCH and we are taking advantage of that to get the last non blank cell in a range.