XMATCH Function

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:

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.

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:

Search mode

The fourth argument for XMATCH is search_mode. This is an optional argument that controls search behavior as follows:

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 PQR-121 appears in row 6 of the range B5:B13. Notice that 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 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 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.