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)


=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:

excel function xmatch

Enter in the XMATCH function:

The formula is here:

excel function xmatch position

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:

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:

excel function xmatch wildcard character
If needed, you can download the Excel file used here: xmatch.xlsx
Note: this function is only available with Office 365.