XMATCH Function in Excel: Explained

In this article, you will learn how to use the XMATCH function in Excel

What does the XMATCH function in Excel do?

The XMATCH function in Excel is a powerful lookup function introduced in Excel 2021. It is an enhanced version of the MATCH function that offers additional capabilities and flexibility. The XMATCH function is primarily used for performing approximate or exact matches within a specified range or array.

How to use the XMATCH function in Excel?

The syntax of the XMATCH function in Excel is as follows:

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

‍

lookup_value: The value you want to find within the lookup_array. This is case insensitive. To use a case sensitive version, use EXACT in combination with XMATCH when specifying this value.

lookup_array: The range or array where you want to search for the lookup_value. Generally, this can only be a single row or a single column.

match_mode (optional): Specifies the match type. It can take the following values:

0 or omitted: Exact match (default).

-1: Exact match or next smaller value.

1: Exact match or next larger value.

search_mode (optional): Specifies the search mode.It can take the following values:

1 or omitted: Search from the first to the last value in the lookup_array (default).

-1: Search from the last to the first value in the lookup_array.

search_direction(optional): Specifies the search direction. It can take the following values:

1 or omitted: Search in ascending order (default).

-1: Search in descending order.

Note: The match_mode,search_mode, and search_direction arguments are only available in certain versions of Excel (Excel for Microsoft 365, Excel 2021, and Excel 2022)

‍

Here's an overview of what the XMATCH function does:

Match Type:

The XMATCH function allows you to specify the match type using the "match_type"argument. It supports three match types:

0 or Exact Match (default): Finds the exact match within the range.

-1 or Exact Match or Next Smallest: Finds the exact match or the next smaller value if an exact match is not found.

1 or Exact Match or Next Largest: Finds the exact match or the next larger value if an exact match is not found.

Lookup Direction:

The XMATCH function allows you to specify the lookup direction using the "search_mode" argument.

1 or Search First to Last (default): Searches from the first to the last value in the range.

-1 or Search Last to First: Searches from the last to the first value in the range.

Handling Errors:

The XMATCH function provides improved error handling compared to the MATCH function.

It returns the #N/A error value if no match is found, instead of returning an error when an approximate match is not available.

The XMATCH function is particularly useful for advanced lookup scenarios, such as performing approximate matches, searching in both directions, and handling dynamic arrays. It provides greater control and versatility compared to the traditional MATCH function, making it a valuable tool for data analysis and manipulation in Excel.