How to Use the XMATCH Function in Google Sheets for Advanced Position Lookups

Locating the exact numeric position of an item within a dataset is a fundamental building block of dynamic spreadsheet models. For years, spreadsheet users relied on the traditional MATCH function. However, the legacy function defaults to approximate matching and requires sorting data in ascending order when used with binary search algorithms. Google Sheets introduced the modern XMATCH function to overcome these limitations.

In this guide, you will learn the syntax, arguments, and practical business use cases of the XMATCH function in Google Sheets, including exact matching, reverse-order searches, wildcard queries, and paired lookups with the INDEX function.

What Is the XMATCH Function?

The XMATCH function searches for a specified item in an array or range of cells and returns the relative numeric position (row or column index) of the item. By default, XMATCH performs an exact match, eliminating the common errors associated with forgetting the 0 match type in traditional MATCH formulas.

Key advantages of XMATCH over traditional MATCH include:

• Exact Match by Default: Automatically looks for exact values without requiring an explicit match argument.
• Bottom-to-Top / Right-to-Left Searching: Easily search backwards through a range to find the last occurrence of an item.
• Wildcard Matching: Native support for asterisks (*) and question marks (?) without special flags.
• Binary Search Flexibility: Supports high-speed binary searches on both ascending and descending sorted datasets.

Syntax and Argument Breakdown

The syntax for the XMATCH function in Google Sheets is as follows:

=XMATCH(search_key, lookup_range, [match_mode], [search_mode])

Argument Details

• search_key (Required): The value you want to locate (text string, number, date, or cell reference).
• lookup_range (Required): The single row or single column range to search within (e.g., A2:A100).
• match_mode (Optional): Controls the type of matching:
  • 0 (Default): Exact match. If not found, returns an #N/A error.
  • -1: Exact match, or next smaller item if no exact match exists.
  • 1: Exact match, or next larger item if no exact match exists.
  • 2: Wildcard match (supports *, ?, and ~).
• search_mode (Optional): Controls the direction and algorithm of the lookup:
  • 1 (Default): Search starting at the first item (top-to-bottom or left-to-right).
  • -1: Search starting at the last item (bottom-to-top or right-to-left).
  • 2: Binary search on data sorted in ascending order.
  • -2: Binary search on data sorted in descending order.

Practical Examples of Using XMATCH

Example 1: Basic Exact Match Lookup

Suppose column A contains employee IDs (e.g., EMP-101 through EMP-150). To locate the relative row index of employee EMP-125:

=XMATCH("EMP-125", A2:A50)

If EMP-125 is located in cell A15, the formula returns 14 (the 14th item in the specified range A2:A50).

Example 2: Finding the Last Occurrence (Reverse Search)

In transactional logs or event audit histories, the latest record typically sits at the bottom of the sheet. Traditional MATCH only returns the first occurrence. With XMATCH, you can search backwards by specifying search_mode = -1:

=XMATCH("Client-Alpha", B2:B200, 0, -1)

This formula scans upwards from cell B200 to B2, instantly returning the position of the most recent interaction with “Client-Alpha”.

Example 3: Partial and Wildcard Matching

When searching through unstructured product descriptions or skus, wildcard patterns allow partial lookups. To find the first product code that starts with “PRO-” and ends with “UK”:

=XMATCH("PRO-*UK", C2:C100, 2)

Setting match_mode to 2 activates wildcard evaluation, where * represents any sequence of characters and ? represents a single character.

Example 4: Next Smaller or Next Larger Matching (Tax Bins & Tiers)

When categorising sales into bonus tiers or tax bands, you often need the closest threshold below or above a given figure without exact equality:

# Find the tier threshold equal to or immediately below £45,000
=XMATCH(45000, D2:D10, -1)

If the tier thresholds in D2:D10 are 10000, 25000, 50000, and 75000, the formula identifies 25000 as the nearest lower threshold and returns its relative position.

Pairing XMATCH with INDEX for Dynamic Two-Way Lookups

While XLOOKUP is excellent for one-dimensional retrievals, pairing INDEX and XMATCH delivers unmatched flexibility for two-dimensional matrix lookups across dynamic rows and columns.

Consider a pricing grid where rows represent Product SKUs (A2:A20) and columns represent Regional Currency Rates (B1:G1). To retrieve the exact price for product “WIDGET-09” in the “GBP” column:

=INDEX(B2:G20, XMATCH("WIDGET-09", A2:A20), XMATCH("GBP", B1:G1))

In this construction:

1. The first XMATCH determines the row index of “WIDGET-09”.
2. The second XMATCH determines the column index of “GBP”.
3. INDEX extracts the cell value located at the exact intersection of that row and column.

Error Handling with IFERROR

If XMATCH cannot locate the search key, it produces a standard #N/A error. Wrap your formula in IFERROR to provide clean, user-friendly feedback in dashboards:

=IFERROR(XMATCH(F2, A2:A100), "Item Not Found")