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