XMATCHreturns the relative position of an item in an array or range that matches a specified value.
- Supports enhanced match and search functionality
- Allows wildcard matches with a question mark (?) or asterisk (*)
XMATCH(search_key, lookup_range, [match_mode], [search_mode])
search_key: The value to search for. For example,
lookup_range: The range to be considered for the search. This range must be a singular row or column.
0by default] The manner in which to find a match for the
0is for an exact match.
1is for an exact match or the next value that's greater than the
-1is for an exact match or the next value that's lesser than the
2is for a wildcard match.
1by default] The manner in which to search through the lookup range.
1is to search from the first entry to the last.
-1is to search from the last entry to the first.
2is to search through the range with binary search. The range needs to be sorted in ascending order first.
-2is to search through the range with binary search. The range needs to be sorted in descending order first.
Lookup table for all examples.
XMATCHon Sales rep column with
XMATCHfor total amount sold with
XMATCHwith horizontal matching.