Exceljet

Quick, clean, and to the point

Excel XMATCH Function

Excel XMATCH function
Summary 

The Excel XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. 

Purpose 
Get the position of an item in a list or table
Return value 
Numeric position in lookup array
Syntax 
=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
Arguments 
  • 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.
Version 
Usage notes 

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

The XMATCH function takes four arguments: lookup_value, lookup_array, match_mode, and search_mode. Lookup_value is the value to look for, and lookup_array is the range or array to search. Both arguments are required. The match_mode argument controls what kind of match is performed (exact, next smallest, next largest, or wildcard), see match_mode below for details. Finally, search_mode controls the search direction - whether XMATCH should start at the beginning of the array, at the end of the array, or if XMATCH should perform a binary search. See search_mode below for details.

XMATCH only supports one-dimensional arrays or ranges, either vertical and horizontal. However, you can use XMATCH to locate values in a two-dimensional range or table by giving XMATCH a single column or row that contains the lookup value (basic INDEX and MATCH example). You can also use MATCH twice in a single formula to find a matching row and column at the same time (INDEX and MATCH example).

XMATCH vs. MATCH

The XMATCH function works just like MATCH function, but with more capability and flexibility. In some cases, XMATCH can be a drop-in replacement for the MATCH function. For example, for exact matches, the syntax is identical:

=MATCH(lookup, range, 0) // exact match
=XMATCH(lookup, range, 0) // exact match

However, for approximate matches, the behavior is different when match type is set to 1:

=MATCH(lookup, range, 1) // exact match or next smallest
=XMATCH(lookup, range, 1) // exact match or next *largest*

In addition, XMATCH allows -1 for match type, which is not available with MATCH:

=XMATCH(lookup, range, -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 first value
-1 Search from last value (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 example shown, XMATCH is used to retrieve the position of "Mars" in a list of planets in the range B6:B14. The formula in G6 is:

=XMATCH(G5,B6:B14) // returns 4

Notice XMATCH defaults to an exact match. If "Mars" G5 was misspelled "Marz", XMATCH would return #N/A.

Match mode behavior

The example below illustrates match mode behavior with a lookup value of 3.1 in E4, and lookup values in B5:B11.

XMATCH match mode examples

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 with wildcard example

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.

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.

Notes

  1. XMATCH can work with both vertical and horizontal arrays.
  2. XMATCH will return #N/A if the lookup value is not found.
XMATCH is a new function available in Excel 365 only.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas