Forum Discussion

davidleal's avatar
davidleal
Iron Contributor
Mar 30, 2023

XMATCH and MATCH return different result for approximate search lookup_array with repetitive values

The following examples produce a different result:   =MATCH(FALSE, {TRUE,TRUE,TRUE},-1) -> 3 =XMATCH(FALSE, {TRUE,TRUE,TRUE},1) -> 1   They are different functions, but serve for the same purpo...
excel
Formulas and Functions
davidleal's avatar
davidleal
Iron Contributor
Mar 30, 2023

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) -> 2

which 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) -> 2

to 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

 

JosWoolley's avatar
JosWoolley
Iron Contributor
Mar 30, 2023

davidleal 

 

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

  • davidleal's avatar
    davidleal
    Iron Contributor
    Mar 30, 2023

    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}) -> 3

    You 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}) -> 3​

     

    On contrary XMATH produces the correct result:

    XMATCH(4,{3,2,1},-1) -> 1

    with 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.

Resources