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
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Mar 30, 2023

davidleal  wrote:  ``Is it the expected behavior,? is it documented somewhere?``

 

Yes and yes.  For XMATCH to have the same behavior as MATCH, do:

 

=XMATCH(0,{3,2,2},1,-2)

 

From the XMATCH help page (click here😞

 

 

With the default search-mode (1), as you used, XMATCH does a linear search from first to last.  So with match-mode -1, XMATCH will find the first value that is an ``exact match or next largest item`` when there are duplicate values that qualify.

 

But MATCH does a binary search.  So with match-type -1, MATCH will find the last value that is ``the smallest value that is greater than or equal to lookup_value`` when there are duplicate values that qualify.

 

So with XMATCH, we must explicitly specify search-mode -2 in order to get the same binary search behavior.

davidleal's avatar
davidleal
Iron Contributor
Mar 30, 2023

JoeUser2004 Thanks it clarifies a lot. Here are some comments:

  1. https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a documentation doesn't specify the search algorithm used, it is assumed based on the sorting requirement.
  2. https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312 documentation doesn't specify the algorithm used for the default option, it is indicated the direction, so it is assumed the linear search.
  3. There is no link where we can find some documentation on how the binary/linear algorithm was implemented in Excel. On the web, you can find some simulations but usually not for the case or repeated values. It requires some deep dive into the https://en.wikipedia.org/wiki/Binary_search_algorithm algorithms. In any case, there is no reference.
  4. As a result of that, I have seen many people stop using XMATCH (even Microsoft indicates it is an improved version) due to performance issues compared to MATCH and the reason is that they don't use the search mode input argument in some scenarios, which results in lower performance, because binary search is faster than linear search for the sorted array.

In summary in my opinion there is significant room for improvement in MATCH/XMATCH documentation.

 

Thanks again for your help on this

Resources