The XMATCH is one of the functions in the recently launched function bundle that includes XLOOKUP, LAMBDA, and Lambda helper functions (LHF) in Google Sheets.
Another exciting new feature we got along with this function bundle is the Named functions.
I’ve already posted the other functions in the bundle, which you can find in my Google Sheets Function Guide.
As a blogger, this launch puts me in a state where I should update several tutorials on this blog to include new solutions to the problems discussed there.
In this tutorial, you can learn the use of the XMATCH function with a few examples in Google Sheets.
The Purpose of the XMATCH Function in Google Sheets
The XMATCH function in Google Sheets searches and returns the relative position of an item (search_key) in a single row or column range (lookup_range).
This function has a few search and match modes to control the behavior of finding the relative position of the item that differentiates it from MATCH.
Syntax and Arguments
Syntax:
XMATCH(search_key, lookup_range, [match_mode], [search_mode])Arguments:
There are four arguments in the XMATCH function in Google Sheets. Out of the four, the last two are optional arguments.
Let’s see below what are the roles of those four arguments and the default values in the two optional arguments.
search_key – The value (item) to search for.
lookup_range – A single row or column to be considered for the search.
match_mode (optional, 0 by default) – The four modes to find a match for the search_key, which is self-explanatory from the table below.
| MATCH MODES | DESCRIPTION |
| 0 | Exact Match |
| 1 | Exact Match or the Next Largest Value |
| -1 | Exact Match or the Next Smallest Value |
| 2 | Wildcard Match |
You may be confused when you compare modes 1 and -1 with the same modes in the MATCH function.
There the said two modes work only in sorted lookup_range. Here it doesn’t require sorting for an approximate match.
search_mode (optional, 1 by default) – The four modes to search through the lookup_range, which is self-explanatory from the table below.
| SEARCH MODES | DESCRIPTION |
| 1 | First Entry to the Last |
| -1 | Last Entry to the First |
| 2 | Binary Search (the lookup range must be sorted in A-Z [ascending] order) |
| -2 | Binary Search (the lookup range must be sorted in Z-A [descending] order) |
How to Use the XMATCH Function in Google Sheets – Examples
Let’s match a date in a date column and get its relative position. That will be easy for us to learn the XMATCH function arguments.
As a side note, you can use the XMATCH function similarly in a row.
For example purposes, I’ve entered a few random dates in column A, which is not sorted in any order, and a few names in column B in the corresponding rows.
Assume it’s a sample hotel room booking data.
How do we perform XMATCH in this table to find the relative position of a given booking date? Also, how to use that output to return the name of the person who booked the room?
You May Like:- Reservation and Booking Status Calendar Template in Google Sheets.
Exact Match and Search From First Entry to Last
Formula 1 (Exact Match):
=xmatch(D3,A3:A12)The above XMATCH formula searches the date 24/09/2022 in A3:A12 and returns the relative position of the matching date, i.e., 4.
We haven’t specified the match or search modes in the above XMATCH formula.
So it takes the default values, i.e., 0 (exact match in match_mode) and 1 (searches from first entry to last in search_mode).
We can use it within the INDEX function to get the name of the person, i.e., Harry, who booked the room on the said date.
XMATCH and INDEX Function Combo:
=index(B3:B12,xmatch(D3,A3:A12),1)Approximate Match and Search From First Entry to Last
What happens when the search_key in cell D3 is 17/09/2022?
There is no match in the list. So the above formula would return #N/A, which represents not available.
If you use match_mode 1, the formula will search for the next largest value available, i.e., 18/09/2022.
Formula 2 (Next Largest):
=xmatch(D3,A3:A12,1)The relative position of the first occurrence of the date 18/09/2022 in the list is 6. So that would be the returned value.
Remember, the formula uses the default search_mode, i.e., first entry to last.
The following INDEX and XMATCH function combo will return the name of the person who booked the room on the next largest date of the search date, i.e., Rachel, on 18/09/2022.
=index(B3:B12,xmatch(D3,A3:A12,1),1)The next smallest date to 17/09/2022 on the list is 14/09/2022.
Formula 3 (Next Smallest):
=xmatch(D3,A3:A12,-1)So obviously, the above XMATCH formula would return 2 as the relative position.
As earlier, we can use it within the INDEX to offset as many rows in B3:B12 and get the corresponding name.
Search From Last Entry to First in the XMATCH Function
All the above XMATCH formulas use the default search_mode 1, i.e., search from the first entry to the last entry.
What about the last entry to the first entry (-1) in them? How do they differ when returning the relative position?
Exact Match:
=xmatch(D3,A3:A12,0,-1)Note:- For the exact match, the date in D3 is 24/09/2022, and for the approximate (next largest and smallest) match, it is 17/09/2022.
This XMATCH formula would return 4 even if the search_mode is the last entry to first since there is no duplicate of the search_key in the list.
It’s a different story below.
Next Largest:
=xmatch(D3,A3:A12,1,-1)The next largest to 17/09/2022 in the list is 18/09/2022. Its relative position is 9 when matching from the bottom (last entry to first).
Next Smallest:
=xmatch(D3,A3:A12,-1,-1)The next smallest to 17/09/2022 in the list is 14/09/2022. Its relative position is 10 when matching from the bottom (last entry to first).
Note: In the XMATCH function, the search from first to last and last to first are relevant when there are duplicate entries in the search column (lookup_range).
Binary Search Modes 2 and -2
For lightning-fast lookup and match, one should sort the lookup_range in ascending or descending order.
Then we can use the binary search_mode 2 or -2 accordingly in the XMATCH function.
The binary search_modes also support the exact, next smallest, or the next largest match.
The match will be from the first entry to the last in an exact match in an A-Z sorted range.
=xmatch("apple",{"apple";"apple";"orange";"orange"},0,2)So the above formula will return 1.
But the match will be from the last entry to the first in an exact match in a Z-A sorted range.
=xmatch("apple",{"orange";"orange";"apple";"apple"},0,-2)So here, the formula will return 4.
What about the next largest or next smallest match in binary search mode in the XMATCH function in Google Sheets?
Please check Tables # 1 and 2. The search key is 17/09/2022.
In the next smallest and the next largest in binary search modes, the XMATCH formula will return the relative position from the rows closest to the search_key.
Please have a look at the above screenshot. The red line shows the would-be position of the date 17/09/2022 in the list.
I’ve marked the next smallest and the next largest matching dates in Table 1 and Table 2 above.
Formulas (Table 1; A-Z):
=xmatch(date(2022,9,17),A3:A12,1,2)Output: 5
=xmatch(date(2022,9,17),A3:A12,-1,2)Output: 4
Formulas (Table 2; Z-A):
=xmatch(date(2022,9,17),D3:D12,1,-2)Output: 6
=xmatch(date(2022,9,17),D3:D12,-1,-2)Output: 7
How to Use Wildcards in XMATCH Function in Google Sheets
The XMATCH function supports wildcards too.
When you are unsure whether column B contains “Total,” “Sub Total,” or “Grand Total” and want to return the last total amount in column C, irrespective of “Total,” “Sub Total,” or “Grand Total,” you can use the following INDEX and XMATCH combo.
=index(C:C,xmatch("*Total*",B:B,2,-1))Please learn this table if you are pretty new to wildcards in Google Sheets.
| Wildcard Symbol | Description | Example |
| * | Represents zero or more characters. | “*Total*” finds Total, Sub Total, or Grand Total. |
| ? | Represents a single character. | “S?ng” finds Sing, Sung, or Sang. |
| ~ followed by ?, *, or ~ | Identifies a wildcard character. | “coming~?” finds “coming?” |
That’s all. Thanks for the stay. Enjoy!
Resources:
 in a single row or column range (lookup_range).){kind=link}