MATCH vs XMATCH

The XMATCH function was added in Microsoft 365 to replace the MATCH function.
Before you start upgrading your existing MATCH formulas to use XMATCH, here are a few considerations.


Different Default Behaviour

The MATCH function defaults to exact match or the next smallest item.
The XMATCH function defaults to exact match.
This list has been sorted into Ascending Order.


Different match_type Numbers

The MATCH function uses 1 for the next smallest.
The XMATCH function uses -1 for the next smallest.
When looking for the next smallest, the list must be sorted into Ascending Order.

The MATCH function uses -1 for the next largest.
The XMATCH function uses 1 for the next largest.
When looking for the next largest, the list must be sorted into Descending Order.


XMATCH - Known Bug

Both the MATCH and the XMATCH functions use 0 for exact match.
The MATCH function can perfom a case sensitive exact match (on any list) by combining it with the EXACT function.
The XMATCH function can only perform a case sensitive exact match WHEN THE LIST IS SORTED.
Why does the list have to be sorted when you are looking for an exact match ?


Exact Match Search Algorithm

Is the XMATCH function usig the same search algorithm as the MATCH function ?
The XMATCH function seems to be using a Binary Search.
More Information required.


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext