Microsoft Excel Office 365
Learn How to Use Microsoft Excel XMATCH Function
In this article, you will learn how to use the Microsoft Excel XMATCH function and its prime function in Microsoft Excel. You will also get to know the Microsoft Excel XMATCH function return value and syntax with the help of some examples.
Microsoft Excel XMATCH Function
The primary function of the Microsoft Excel XMATCH function is to get the position of an item in a list or table. That means with the help of the XMATCH function; you can perform a lookup and returns a position in vertical or horizontal ranges. The XMATCH is the more robust and flexible counterpart of the MATCH function. The XMATCH function supports the approximate and exact matches, reverse search, and wildcards (*?) for partial matches.
Return Value of XMATCH Function
The return value will be the numeric position in the lookup array.
Syntax of XMATCH Function
=XMATCH(lookup-value, lookup-array, [match-mode], [search-mode])
Where the arguments:
lookup-value: This is the lookup values.
lookup-array: This is the array or range to search.
match-mode: 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match (optional).
search-mode: 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending (optional).
How to Use Microsoft Excel XMATCH Function?
The Microsoft Excel XMATCH function performs a lookup and returns a position. XMATCH can perform lookups in vertical or horizontal ranges and is meant to be a more flexible and powerful successor to the MATCH function. XMATCH supports both approximate and exact matches and wildcards (* ?) for partial matches. Like the XLOOKUP function, XMATCH can search data starting from the first value or the last value (i.e., reverse search). Finally, XMATCH can perform binary searches, which are specifically optimized for speed.
Match Type Behavior
0 (default) Exact match, and return #N/A if no match
-1 Exact match or next smaller item
1 Exact match or next larger item
2 Wildcard match
Search Mode Behavior
1 (default) Search from the first value
-1 Search from last value
2 Binary search values sorted in an ascending order
-2 Binary search values sorted in the descending order
Check out the list of all other articles:
- Learn How to Get nth Match in Microsoft Excel
- Learn How to Find Missing Values in Microsoft Excel
- Learn How to Get nth Smallest Value With Criteria in Microsoft Excel
- Learn How to Sort by Text Length in Microsoft Excel
- Learn How to Sort by Two Columns in Microsoft Excel
- Learn How to Sort by Random in Microsoft Excel
- Learn How to Sort by One Column in Microsoft Excel
- Learn How to Sort by Custom List in Microsoft Excel