How to use the Excel XMATCH function

Summary

The Excel XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches.

Purpose

Lookup values in range or table

Return value

Result as array

Syntax

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

Arguments

lookup_value - The lookup value.
lookup_array - The array or range to search.
match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

Usage notes

The Excel XMATCH function performs a lookup and returns a position. XMATCH can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to the MATCH function. XLOOKUP supports both approximate and exact matches, and wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (reverse search). See match type and search mode below for details.

Match type

The third argument for XLOOKUP is match_type. This is an optional argument that controls match behavior as follows:

Match type	Behavior
0 (default)	Exact match. Will return #N/A if no match.
-1	Exact match or next smaller item.
1	Exact match or next larger item.
2	Wildcard match (*, ?, ~)

Search mode

The fourth argument for XLOOKUP is search_mode. This is an optional argument that controls search behavior as follows:

Search mode	Behavior
1 (default)	Search from first value
-1	Search from last value (reverse)
2	Binary search values sorted in ascending order
-2	Binary search values sorted in descending order

Binary searches are very fast, but take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

Example #1 - exact match

In the example shown, XMATCH is used to retrieve the position of "Mars" in a list of planets in the range B6:B14. The formula in G6 is:

=XMATCH(G5,B6:B14) // returns 4

Notice XMATCH defaults to an exact match. If "Mars" G5 was misspelled "Marz", XMATCH would return #N/A.

Example #2 - match behavior

The example below illustrates match mode behavior with a lookup value of 3.1 in E4, and lookup values in B5:B11.

XMATCH match mode examples

E6=XMATCH(E4,B5:B11) // returns #N/A
E7=XMATCH(E4,B5:B11,-1) // returns 3
E8=XMATCH(E4,B5:B11,1) // returns 4

Example #3 - INDEX and XMATCH

XMATCH can be used just like MATCH with the INDEX function. To retrieve the diamater of Mars based on the original example above, the formula is:

=INDEX(C6:C14,XMATCH(G5,B6:B14)) / returns 6792

Notes

XMATCH can work with both vertical and horizontal arrays.
XMATCH will return #N/A if the lookup value is not found.

XMATCH is a new function available in Office 365 only.

Related functions

Excel XLOOKUP Function

The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.