search-type: An optional argument that specifies the order in which to search the range.
Binary descending (-2): Binary search that requires the range to be sorted in descending order, otherwise returns an error.
Last to first (-1): Search the range from last to first.
First to last (1 or omitted): Search the range from first to last.
Binary ascending (2): Binary search that requires the range to be sorted in ascending order, otherwise returns an error.
If search-range is a spanning reference (such as "B"), headers and footers are automatically ignored.
Examples
Given the following table:
A
B
C
1
Name
Age
Salary
2
Amy
35
71000
3
Matthew
27
81000
4
Chloe
42
86000
5
Sophia
51
66000
6
Kenneth
28
52000
7
Tom
49
62000
8
Aaron
63
89000
9
Mary
22
34000
10
Alice
29
11
Brian
52500
=XMATCH(49000,C2:C11,1) searches the range for the first salary that is either exactly "49000" or is the next largest salary after "49000," and returns "5" to represent the fifth item in the range ("52000").
=XMATCH(30,B2:B11,-1) searches the range for the first employee who is exactly 30 or the next youngest age, and returns "9" to represent the ninth item in the range ("29").
=XMATCH(REGEX("^S.*"), A2:A11,2) searches the range for the first employee whose name starts with S, and returns "4" to represent the fourth item in the range ("Sophia").