Excel XMATCH formula

Excel: 2021, 365, for Mac and web

Excel appeared in 1987, and only after 34 years create the formula XMATCH. Function/Formula 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. Excel XMATCH formula je detaljno objašnjena na Office stranici za podršku korisnicima.

Example of use

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.  XMATCH supports both approximate and exact matches, and wildcards (* ?) for partial matches. Like the XLOOKUP function, XMATCH can search data starting from the first value or the last value (i.e. reverse search). Finally, XMATCH can perform binary searches, which are specifically optimized for speed.

The XMATCH function takes four arguments: lookup_value, lookup_array, match_mode, and search_mode. Lookup_value is the value to look for, and lookup_array is the range or array to search. Both arguments are required. The match_mode argument controls what kind of match is performed (exact, next smallest, next largest, or wildcard), see match_mode below for details. Finally, search_mode controls the search direction – whether XMATCH should start at the beginning of the array, at the end of the array, or if XMATCH should perform a binary search. See search_mode below for details.

XMATCH only supports one-dimensional arrays or ranges, either vertical and horizontal. However, you can use XMATCH to locate values in a two-dimensional range or table by giving XMATCH a single column or row that contains the lookup value (basic INDEX and MATCH example). You can also use MATCH twice in a single formula to find a matching row and column at the same time.

XMATCH vs. MATCH

The XMATCH function works just like MATCH function, but with more capability and flexibility. In some cases, XMATCH can be a drop-in replacement for the MATCH function. For example, for exact matches, the syntax is identical

Syntax