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 3rd 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)``