XMATCH function

XMATCH returns the relative position of an item in an array or range that matches a specified value. XMATCH :

Sample usage

XMATCH("Apple", A2:A)

XMATCH("Price", A2:A)

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

search_key : The value to search for. For example, 42 , "Cats" , or B24 .
lookup_range : The range to consider for the search. This range must be a singular row or column.
match_mode : [OPTIONAL: 0 by default] The manner in which to find a match for the search_key .
- 0 is for an exact match.
- 1 is for an exact match or the next value that's greater than the search_key .
- -1 is for an exact match or the next value that's lesser than the search_key .
- 2 is for a wildcard match.
search_mode : [OPTIONAL: 1 by default] The manner in which to search through the lookup range.
- 1 is to search from the first entry to the last.
- -1 is to search from the last entry to the first.
- 2 is to search through the range with binary search. The range needs to be sorted in ascending order first.
- -2 is to search through the range with binary search. The range needs to be sorted in descending order first.

Lookup table for all examples.

XMATCH on Sales rep column with match_mode and search_mode omitted.

XMATCH for Total Amount sold with match_mode = 0 and search_mode = 1 and -1 .

XMATCH with horizontal matching.

XMATCH with match_mode = 1 and match_mode = -1 and search_mode omitted.