Excel Function: XMATCH

The XMATCH function returns the position of an item in an array or a cell range.

It is an improved version of the MATCH function.

Usage:

=XMATCH(lookup_value, lookup_array)

or

=XMATCH(lookup_value, lookup_array, match_mode, search_mode)

Example of use

The goal here is to return the position of the searched city in the city table:

Enter in the XMATCH function:

• lookup_value: the value whose position to search
• lookup_array: the array in which to search the position of lookup_value

The formula is here:

=XMATCH(C2,A2:A11)

In this example, "London" is indeed the 3^rd value in the range A2 to A11.

Optional arguments

In the previous example, only the 2 mandatory arguments were filled in, but there are 2 more:

• match_mode: the method for finding a match:
  • 0: exact match (by default)
  • 1: exact match or next smaller item
  • -1: exact match or next larger item
  • 2: wildcard match (where * replaces zero, one or several characters, ? replaces a character and ~ allows to escape one of these 3 characters *?~)
• search_mode: the search mode:
  • 1: search from first to last (by default)
  • -1: search from last to first
  • 2: binary search assuming the range is sorted in ascending order
  • -2: binary search assuming the range is sorted in descending order

Here is another example with match_mode at 2 to use the wildcard character * (which replaces zero, one or several characters) and search_mode at -1 to search the position of the city starting with "D" from the end:

=XMATCH(C2&"*",A2:A11,2,-1)