An Overview and How to Use the XMATCH Function in Google Sheets

2025年1月18日

Google Sheets offers several convenient functions for searching data and retrieving its position.

Among them, the XMATCH function is a new and improved version of the traditional MATCH function.

In this article, we will explore the differences between XMATCH and MATCH functions, as well as the basic usage of XMATCH.

合同会社橋爪オフィス

Master XLOOKUP: Simplify Data Search and Learn the Key Differences from VLOOKUP and HLOOKUP | 合同会… Have you ever found yourself struggling to search for data in Google Sheets, especially when using traditional functions

TOC

Comparison of XMATCH and MATCH Functions

Feature	XMATCH	MATCH
Search Direction	Can specify both vertical and horizontal search	Vertical search only
Match Types	Supports exact match, approximate match, and partial match	Supports exact match and approximate match
Error Handling	Allows custom return values if no match is found	Requires functions like IFERROR to handle errors
Flexible Use Cases	Suitable for both simple and complex searches	Best suited for simple searches

The XMATCH function supports both vertical and horizontal searches.
It also allows partial match searches.

Both the XMATCH and MATCH functions are designed to search for a specified value and return its position.

However, XMATCH offers greater flexibility and enhanced functionality.

Specify Search Order (Ascending or Descending): With the XMATCH function, you can specify the search order as either ascending or descending.
This feature enables efficient data searches regardless of how the list is sorted.
Flexible Searches with Partial Matches:: XMATCH allows partial matches, enabling you to perform approximate searches or find data that matches only part of a string.
This is especially useful when multiple possibilities exist, or when you don’t know the exact value you’re searching for.

XMATCH Syntax and Basic Usage

XMATCH Syntax

The basic syntax of the XMATCH function is as follows:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

lookup_value: The value you want to search for.
lookup_array: The range where the search will be performed.
[match_mode] (optional): Specifies the type of match (exact match, approximate match, etc.).
[search_mode] (optional): Allows you to set the search order, either top-to-bottom or bottom-to-top.

Match Types and Search Modes

Match Types

You can specify whether to perform an exact or approximate match:

0: Exact match.
1 or -1: Approximate match.

This allows you to retrieve the closest value when an exact match isn’t found, making it useful for working with ranges or approximate data.

Search Modes

The search mode determines the order in which the search is conducted:

1: Searches sequentially from the first to the last value.
-1: Searches in reverse, starting from the last value and moving upward.

This feature gives you control over the order of results when there are multiple occurrences of the same value, helping you retrieve the desired data efficiently.

Basic Usage

Search in Ascending Order

For example, if you want to search for the position of a specific product name in a product list from top to bottom:

C2: The product name you want to search for.
D2: The position of the product name.

To return the position of the product name in D2, you can use the following XMATCH formula:

=XMATCH(C2, A2:A, 0, 1)

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

This formula searches for the product name “Apple” in ascending order (top to bottom) and returns the position of the first match it finds.

In this case, the first occurrence of “Apple” is in cell A3 (the second product name), so the XMATCH function returns 2.

Descending Search

Next, let’s demonstrate how to use the XMATCH function to search for a product name starting from the bottom of the list.

From the previous example, enter the following XMATCH formula in cell D2:

=XMATCH(C2, A2:A, 0, -1)

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

This formula searches for the product name “Apple” in descending order (bottom to top).

As a result, the last occurrence of “Apple” is found in cell A7 (the 6th product in the list), so the XMATCH function returns 6.

Conclusion

The XMATCH function is more flexible than the traditional MATCH function, enabling more advanced data analysis.

For simple searches, the MATCH function is often sufficient. However, when you need to perform searches regardless of the data’s order or use partial matches to find similar terms, the XMATCH function proves to be a powerful and convenient tool.