MATCH vs XMATCH

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


Reasons to use XMATCH

The new function can return the position of a value in both a vertical and horizontal list.
The new function can perform partial matches.
The new function can use a binary search algorithm.


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.

microsoft excel docs

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.

microsoft excel docs

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.

microsoft excel docs

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 ?

microsoft excel docs

Exact Match Search Algorithm

Is the XMATCH function using the same search algorithm as the MATCH function ?
The XMATCH function can perform a Binary Search.
More Information required.


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