Function: XMATCH

The XMATCH function is a Lookup & Reference function that returns the relative position of a lookup value from a specified array or range. In this guide, we’re going to show you how to use the XMATCH function and also go over some tips and error handling methods.

Supported versions

Office 365 Subscribers only

Syntax for the XMATCH Function

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

Arguments

lookup_value	The value you are looking for
lookup_array	Where you are looking - the array or range where you want to look up the value
[match_mode]	Optional. A predefined number specifying the match type: 0: Exact match. If none found, return #N/A. This is the default option. -1: Exact match. If none found, return the next smaller item. 1: Exact match. If none found, return the next larger item. 2: A wildcard match where *, ?, and ~ characters have a special purpose.
[search_mode]	Optional. A predefined number specifying the search mode: 1: Perform a search starting at the first item. This is the default option. -1: Perform a reverse search starting with the last item. 2: Perform a binary search that relies on the lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. 2: Perform a binary search that relies on the lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Examples of XMATCH Function

Basic usage

The XMATCH needs 2 arguments to work with minimum requirements: lookup_value, lookup_array. The function searches the lookup_value in the lookup_array. If the value is found, the function returns the relative position, starting from the first one. Finding the exact value starting from the top/left item is the default behavior of the XMATCH function.

XMATCH(Name,Table1[Name])

Approximate match

Approximate searches for approximate results if the exact match is not found. Set a value for the [match_mode] argument to return the next smaller or greater value, unless the exact value is found.

The [match_mode]’s default value is 0, which specifies exact matching. Give -1 or 1 to return the next smaller or greater value, respectively.

The next smaller: =XMATCH(Id,Table5['#],-1)The next greater: =XMATCH(Id,Table5['#],1)

Search direction and Wildcard support in XMATCH Function

The XMATCH function can use wildcard characters and search in any direction. Since a wildcard search is a type of approximate search, it needs to be specified in the [match_type] argument. Use 2 to activate wildcard search.

The search direction, on the other hand, is determined by the [search_mode] argument, which can take 1 (default) or -1. Omit the parameter, or use 1 for searching from top or left value. -1 is for searching in the "last item to first item" direction.

In the following example, the XMATCH functions returns coordinates for the INDEX functions. The search value is a string that begins with “Sp.”. So, it could be either “Sp. Atk” or “Sp. Def” depending on the search direction.

The second XMATCH function searches starting from the left for the first formula.

=INDEX(Table6,XMATCH(Name,Table6[Name]),XMATCH(Category,Table6[#Headers],2,1))

The second formula uses -1 for the second XMATCH. Thus, the search starts from the right.

=INDEX(Table6,XMATCH(Name,Table6[Name]),XMATCH(Category,Table6[#Headers],2,-1))

Download Workbook

Tips

XMATCH is an improved version of the MATCH formula.
You can do both vertical and horizontal lookups.
Unlike MATCH, you are not limited to a left-to-right lookup. You can search staring from the last item in the list.

Issues

#N/A

If the XMATCH function cannot find the lookup value.

#REF!

If the XMATCH is using a range from another workbook, the other workbook should be open. Otherwise, the XMATCH will return #REF! error.