Google Sheets Function: XMATCH

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

This function is an enhanced version of the MATCH function.


=XMATCH(search_key, lookup_range)


=XMATCH(search_key, lookup_range, match_mode, search_mode)

Example of use

The XMATCH function will here return the position of the searched city in the range of cities:

google sheets xmatch function

Enter into the XMATCH function:

The formula here is:

google sheets xmatch function position

In this example, "Dublin" is indeed the seventh value in the range A2 to A11.

Optional Arguments

In the previous example, only the 2 mandatory arguments were specified, but there are 2 more optional ones:

Here's another example using the wildcard character "*" (which replaces no, one, or multiple characters) and the search mode that starts from the end, to search for the position of a city that starts with "C" from the end:

google sheets xmatch function wildcard character
If needed, you can copy the Google Sheets document (or view the document) with these examples.