Purpose
Return value
Syntax
=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])
 lookup_value  The lookup value.
 lookup_array  The array or range to search.
 match_mode  [optional] 0 = exact match (default), 1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
 search_mode  [optional] 1 = search from first (default), 1 = search from last, 2 = binary search ascending, 2 = binary search descending.
How to use
The XMATCH function performs a lookup and returns the numeric position of the lookup value as a result. XMATCH takes four arguments, and the generic syntax looks like this:
=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])
Lookup_value is the value to look for, and lookup_array is the range or array to look in. The match_mode argument controls what kind of match is performed (exact, next smallest, next largest, or wildcard). Search_mode controls the search direction (first to last or last to first) and enables the binary search option, which is optimized for speed. See below for more details.
Example
To perform an ordinary exact match, only the first two arguments are required. For example, to locate the position of the planet Mars in the worksheet shown, you can use XMATCH like this:
=XMATCH(G4,B5:B13) // returns 4
The result is 4 since "Mars" appears in the fourth row of the range B5:B13. Typically, the XMATCH function is used together with the INDEX function to retrieve a value at a specific location. For example, to find the diameter of Mars, you can combine INDEX and XMATCH like this:
=INDEX(C5:C13,XMATCH(G4,B5:B13)) // returns 6792
In this formula, XMATCH returns the position of Mars (4) to INDEX, and INDEX returns the 4th value in the range C5:C13 (6,792) as the final result.
XMATCH vs. MATCH
XMATCH is an upgraded replacement for the MATCH function. Like the MATCH function, XMATCH performs a lookup and returns a numeric position. Also like MATCH, XMATCH can perform lookups in vertical or horizontal ranges, supports both approximate and exact matches, and allows wildcards (* ?) for partial matches. The 5 key differences between XMATCH and MATCH are:

XMATCH defaults to an exact match, while MATCH defaults to an approximate match.

XMATCH can find the next larger item or the next smaller item.

XMATCH can perform a reverse search (i.e. search from last to first).

XMATCH does not require values to be sorted when performing an approximate match.

XMATCH can perform a binary search, which is specifically optimized for speed.
Replacing MATCH with XMATCH
In some cases, XMATCH can be a dropin replacement for the MATCH function. For example, for exact matches, the syntax is identical:
=MATCH(value,array,0) // exact match
=XMATCH(value,array,0) // exact match
However, for approximate matches, the behavior is different when match_type is set to 1:
=MATCH(value,array,1) // exact match or next smallest
=XMATCH(value,array,1) // exact match or next *largest*
In addition, XMATCH allows 1 for match type, which is not available with MATCH:
=XMATCH(value,array,1) // exact match or next smallest
Note: the MATCH function does not offer the search mode argument at all.
Match mode
The third argument for XMATCH is match_mode. This is an optional argument that controls match behavior as follows:
Match mode  Behavior 

0 (default)  Exact match. Will 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
The fourth argument for XMATCH is search_mode. This is an optional argument that controls search behavior as follows:
Search mode  Behavior 

1 (default)  Search from the first value to the last 
1  Search from the last value to the first (reverse) 
2  Binary search values sorted in ascending order (very fast) 
2  Binary search values sorted in descending order (very fast) 
Binary searches are very fast, but take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.
Exact match
In the worksheet below, XMATCH is used to get the position of "Mars" in a list of planets in the range B6:B13. The formula in G6 is:
=XMATCH(G4,B5:B13) // returns 4
XMATCH defaults to an exact match, so there is no need to enable this behavior.
Match mode behavior
The example below illustrates match mode behavior with a lookup value of 3.1 and lookup values in B5:B11:
E6=XMATCH(E4,B5:B11) // returns #N/A
E7=XMATCH(E4,B5:B11,1) // returns 3
E8=XMATCH(E4,B5:B11,1) // returns 4
 In cell E6, match_mode defaults to 0 (exact match) so the result is #N/A (not found).
 In cell E7, match_mode is given as 1 (next smallest) so the result is 3.
 In cell E8, match_mode is given as 1 (next largest) so the result is 4.
INDEX and XMATCH
XMATCH can be used just like MATCH in a normal INDEX and MATCH formula. To retrieve the diameter of Mars based on the original example above, the formula is:
=INDEX(C6:C14,XMATCH(G5,B6:B14)) / returns 6792
For more information, see How to use INDEX and MATCH.
XMATCH with wildcard
When match_mode is set to 2, XMATCH can perform a match using wildcards. In the example shown below, the formula in E5 is:
=XMATCH(E4,B5:B13,2) // returns 6
This is equivalent to:
=XMATCH("pq*",B5:B13,2)
XMATCH locates the first code that begins with "pq" and returns 6 since PQR121 appears in row 6 of the range B5:B13. Notice that XMATCH is not casesensitive.
Multiple criteria
There is no builtin way to provide multiple criteria with XMATCH but you can use Boolean logic to apply multiple conditions. In the worksheet below, XMATCH is configured to apply 3 separate conditions for
For more details, see this page.
Casesensitive match
The MATCH function is not casesensitive. However, MATCH can be configured to perform a casesensitive match when combined with the EXACT function in a generic formula like this:
=XMATCH(TRUE,EXACT(lookup_value,array),0))
The EXACT function compares every value in array with the lookup_value in a casesensitive manner. This formula is explained in an INDEX and MATCH example here. The example uses the MATCH function, but XMATCH can be substituted with the same result.
Binary search
XMATCH has a binary search option that runs very quickly. To enable binary search mode, data must be sorted in ascending or descending order. If values are sorted in ascending order, use the value 2 for search_mode. If values are sorted in descending order, use the value 2. Below is the generic syntax to enable binary search mode for an exact match lookup:
=XMATCH(value,array,0,2) // binary search AZ
=XMATCH(value,array,0,2) // binary search ZA
For a more detailed example, see this page. The primary example is based on the XLOOKUP function but the explanation shows how to use INDEX and XMATCH to solve the same problem.
Notes
 XMATCH can work with both vertical and horizontal arrays.
 XMATCH will return #N/A if the lookup value is not found.

XMATCH defaults to an exact match, while MATCH defaults to an approximate match.

XMATCH can find the next larger item or the next smaller item.

XMATCH can perform a reverse search (i.e. search from last to first).

XMATCH does not require values to be sorted when performing an approximate match.

XMATCH can perform a binary search, which is specifically optimized for speed.