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.
- Office 365 Subscribers only
Syntax for the XMATCH Function
|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|
Optional. A predefined number specifying the match type:
Optional. A predefined number specifying the search mode:
Examples of XMATCH Function
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.
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.
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.
The second formula uses -1 for the second XMATCH. Thus, the search starts from the right.
- 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.
- If the XMATCH function cannot find the lookup value.
- If the XMATCH is using a range from another workbook, the other workbook should be open. Otherwise, the XMATCH will return #REF! error.