XMATCH

Excel Functions

Get the position of an item in a list or table

Syntax

EXCEL

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

Arguments

Parameter	Description
`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, 3 = regex match.
`search_mode`	[optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

Return Value

Numeric position in lookup array

Details

XMATCH is a modern replacement for the MATCH function. It is a flexible and versatile function with a number of useful features: The XMATCH function performs a lookup and returns the numeric position of the lookup value as a result. XMATCH takes four arguments, and the generic syntax looks like this: Lookup_value is the value to look for, and lookup_array is the range or array to look in. The match_mode argument controls what kind of match is performed (exact, next smallest, next largest, or wildcard). Search_mode controls the search direction (first to last or last to first) and enables the binary search option, which is optimized for speed. See below for more details. To perform an ordinary exact match, only the first two arguments are required. For example, to locate the position of the

Examples

Example 1

The XMATCH function performs a lookup and returns the numeric position of the lookup value as a result. XMATCH takes four arguments, and the generic s

EXCEL

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

Example

To perform an ordinary exact match, only the first two arguments are required. For example, to locate the position of the planet Mars in the worksheet

EXCEL

=XMATCH(G4,B5:B13) // returns 4

Example

The result is 4 since "Mars" appears in the fourth row of the range B5:B13. Typically, the XMATCH function is used together with the INDEX function to

EXCEL

=INDEX(C5:C13,XMATCH(G4,B5:B13)) // returns 6792

Replacing MATCH with XMATCH

In some cases, XMATCH can be a drop-in replacement for the MATCH function. For example, for exact matches, the syntax is identical:

EXCEL

=MATCH(value,array,0) // exact match
=XMATCH(value,array,0) // exact match

Replacing MATCH with XMATCH

However, for approximate matches, the behavior is different when match_type is set to 1:

EXCEL

=MATCH(value,array,1) // exact match or next smallest
=XMATCH(value,array,1) // exact match or next *largest*

Replacing MATCH with XMATCH

In addition, XMATCH allows -1 for match type, which is not available with MATCH:

EXCEL

=XMATCH(value,array,-1) // exact match or next smallest

XMATCH

Syntax

Arguments

Return Value

Details

Examples

See Also