XMATCH Function

The XMATCH function performs a lookup and returns the numeric position of the lookup value as a result. XMATCH takes four arguments: lookup_value, lookup_array, match_mode, and search_mode. 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. Both arguments are required. 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 the binary search option, which is optimized for speed.  Match_mode and search_mode are both optional, see details below.

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:

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

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

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

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

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

In an INDEX and MATCH formula, using XMATCH instead of the MATCH function "upgrades" the formula to include the benefits listed above.

Replacing MATCH with XMATCH

In some cases, XMATCH can be a drop-in 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
-2	Binary search values sorted in descending order

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

INDEX and XMATCH

XMATCH can be used just like MATCH with the INDEX function. To retrieve the diameter of Mars based on the original example above, the formula is:

=INDEX(C6:C14,XMATCH(G5,B6:B14)) / returns 6792

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 PQR-121 appears in row 6 of the range B5:B13. Notice XMATCH is not case-sensitive.

Multiple criteria

There is no built-in 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.

Case-sensitive match

The MATCH function is not case-sensitive. However, MATCH can be configured to perform a case-sensitive 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 case-sensitive 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 mode option that performs 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 A-Z
=XMATCH(value,array,0,-2) // binary search Z-A

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

1. XMATCH can work with both vertical and horizontal arrays.
2. XMATCH will return #N/A if the lookup value is not found.

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

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

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

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

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