The XMATCH function in Google Sheets finds the relative position of an item within a range that matches a specified criterion. The basic syntax for the XMATCH function is as follows:
=XMATCH(search_key, lookup_range, [match_mode], [search_mode])
Here's a breakdown of the syntax:
search_key: The value you're searching for. This can be a number, text, or a cell reference.
lookup_range: The range of cells where the function searches for the search_key. It must be a single row or column.
match_mode (optional): Specifies the type of match. The default (0) is an exact match. Other options include 1 for an exact match or the next larger item, -1 for an exact match or the next smaller item, and 2 for a wildcard match.
search_mode (optional): Determines the direction of the search. The default (1) searches from the first to the last item. Other options include -1 for a reverse search, and 2 or -2 for binary searches in ascending or descending order, respectively.
Below are examples of using XMATCH in Google Sheets:
To find the exact position of a specific item, you might use a formula like =XMATCH("Alex", A2:A100). This searches for "Alex" in the range A2:A100.
If you're looking for an exact match or the next larger value, you could use =XMATCH(25000, B2:B11, 1), assuming you're searching for the value 25000 in the range B2:B11 and want the position of the first item that is either exactly 25000 or the next larger value.
For partial matches using wildcards, the formula might look like =XMATCH("Peter*", A2:A11, 2), where you're searching for any name that starts with "Peter" in the range A2:A11.
XMATCH can be paired with the INDEX function to return the actual value at the found position.
Suppose you have a spreadsheet with two columns: Column A lists employee names, and Column B lists their corresponding sales figures for a month.
You want to find the sales figure for a specific employee named "Jane Doe". Assume "Jane Doe" is located in cell D1 (as the search key), the names are in range A2:A10, and the sales figures are in range B2:B10.
The XMATCH function can find the relative position of "Jane Doe" in the list of employee names. The INDEX function can then use this position to retrieve the corresponding sales figure from the sales figures column.
You can use the following formula:
=INDEX(B2:B10, XMATCH(D1, A2:A10))
This will return the sales figure for Jane Doe, as shown below.
We hope that this article has helped you and given you a better understanding of how to use XMATCH in Google Sheets. If you enjoyed this article, you might also like our articles on how to fix the problem of changes not saving in Google Sheets and how to use Google Sheets FILTER with a wildcard.
