This guide will discuss how to use XMATCH function in Google Sheets.
When we want to find the relative position of a value within a range, we can utilize the XMATCH function in Google Sheets.
The rules for using the XMATCH function in Google Sheets are the following:
- The lookup_range argument must be either a single row or a single column. The
XMATCHfunction does not support an array with multiple rows and columns. - When the exact search mode is used, but no match is found, the
XMATCHfunction returns a #N/A error. - The
XMATCHfunction allows wildcard matches with an asterisk (*) or a question mark (?).
Google Sheets recently launched the XMATCH function, which is the enhanced version of the MATCH function with more options for search methods.
The MATCH function is used to return the relative position of an item in a given single row or column, however, to ensure an exact match, you have to specify the search type.
However, the XMATCH function does not require us to specify the search type for an exact match. By default, the function chooses the exact match.
In this guide, we will provide a step-by-step tutorial on how to use the XMATCH function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the XMATCH Function
The syntax or the way we write the XMATCH function is as follows:
=XMATCH(search_key,lookup_range,[match_mode],[search_mode])
- = the equal sign is how we begin any function in Google Sheets.
- XMATCH() is our
XMATCHfunction. This function returns the relative position of an item in a given array or range that matches a specified value. - search_key is a required argument. This refers to the value we are searching or looking for.
- lookup_range is another required argument. This is the range we will use to search for the given value. Furthermore, the range must be a single row or column.
- match_mode is an optional argument. This refers to the manner in which to find a match for the search_key value. By default, the function will search for the exact match.
- search_mode is another optional argument. This refers to the manner in which to search through the lookup_range. By default, the function will search from the first entry to the last entry in the given range.
The Anatomy of the INDEX Function
The syntax or the way we write the INDEX function is as follows:
=INDEX(array;row_num;[column_num])
- = this is how we start any function in Google Sheets.
- INDEX() is our
INDEXfunction. This function will return a reference of a cell or value from a particular row and column based on the given range. - array is a required argument. This refers to a range of cells or an array constant.
- row_num refers to the selected row in the array or reference from where we will return a value. This is a required argument when the column_num is omitted.
- column_num refers to the selected column in the array or reference from where the returned value is obtained. Furthermore, this is required when the row_num is omitted.
Match and Search Options
The biggest advantage of using the XMATCH function instead of the MATCH function is the enhanced match and search functionality.
Thus, the XMATCH function has more search and match modes to control the manner in searching for the relative position of the item.
Firstly, the match_mode argument lets us decide how to find a match for the given value. The below shows the different modes we can choose from:
| Match Mode Options | Match Mode Behavior |
|---|---|
| 0 | This is the default option. The function will find the exact match. |
| 1 | The function will find the exact match or the next value that is bigger than the given search_key value. |
| -1 | The function will find the exact match of the next value that is lower than the given search_key value. |
| 2 | The function will find a wildcard match. |
Next, the search_mode argument is used to decide how to search through the given range. The table below shows the different modes we can choose from:
| Search Mode Options | Search Mode Behavior |
|---|---|
| 1 | This is the default option. The function will search from the first entry to the last entry. |
| -1 | The function will search from the last entry to the first entry. |
| 2 | The function will perform a binary search. The range must be sorted in ascending order first. |
| -2 | The function will perform a binary search. The range must be sorted in descending order first. |
A Real Example of Using XMATCH Function in Google Sheets
Let’s say we have a data set about the room reservations of a hotel in a given month. Our initial data set would look like this:
The spreadsheet above shows the name of the customers and the date they are booking the room. For instance, we want to look for the customer who booked a room on a specific date.
We can easily find the relative position of the customer based on a specific date using the XMATCH function.
The formula we will use to perform the task is:
=XMATCH(E1,A2:A10,0,1)
The first part of the formula is the search_key argument or the value we are looking for. In this case, we are looking for the date 19/1/2023, which is in cell E1.
Next, we selected the range A2:A10, the column we will use to search for the specific date. Then, we will input 0 as our match_mode argument since we want the exact match of the date.
Lastly, we will input 1 as our search_mode argument to search from the first cell to the last cell in the range.
The formula will return the relative position of the specific date. In this example, the date is found in the 5th cell of the given range.
However, we can also return the name of the matching date using the INDEX function. The XMATCH function will return the relative position of the specific value then the INDEX function will return the item matching the specific value.
The formula we will use:
=INDEX(B2:B11,XMATCH(E1,A2:A11,0,1))
We simply selected the range containing the value we want to return. Since we want to return the name of the customer, we simply inputted the range B2:B11. Afterward, we can insert our XMATCH formula from above.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the XMATCH function in Google Sheets.
How to Use XMATCH Function in Google Sheets
1. First, we will create a search tab where we will input the value we are looking for. In this case, we will look for the date “19/1/2023”.
2. We will type in our XMATCH formula in an empty cell. To begin, we will type in the function name and select the cell containing the date, which will be “=XMATCH(E1”.
3. Then, we will select the range we will use to look for the date, which will become “=XMATCH(E1,A2:A10”.
4. Lastly, we will input our match and search mode. Since we want the exact match and to start searching from the first entry, we will input 0 and 1, respectively. The final formula will be “=XMATCH(E1,A2:A10,0,1)”.
5. We will press the Enter key to return the final result.
6. Furthermore, we can insert our formula within the INDEX function to return the name matching the specific date. To do this, we will first type in the INDEX function and select the range containing the name, giving us “=INDEX(B2:B11”.
7. Then, we can simply insert our XMATCH formula. Thus, our final formula would be “=INDEX(B2:B11,XMATCH(E1,A2:A11,0,1))”.
8. We will press the Enter key to return the result.
And tada! We have successfully used the XMATCH function in Google Sheets.
You can apply this guide whenever you need to find the relative position of a given value. You can now use the XMATCH function and the various other Google Sheets formulas available to create great worksheets that work for you.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
