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.
=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:
In this example, "London" is indeed the 3rd value in the range A2 to A11.
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: