In this article:

XMATCH in Google Sheets: The Ultimate Guide for 2024

May 8, 2024

How to Use XMATCH in Google Sheets

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:

Finding an Exact Match

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.

xmatch google sheets

Using Match Modes

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.

google sheets xmatch

Performing a Partial Match

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 in google sheets

Combining with the INDEX Function

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->