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.

##### Table of Contents

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`XMATCH`

function does not support an array with multiple rows and columns. - When the exact search mode is used, but no match is found, the
`XMATCH`

function returns a #N/A error. - The
`XMATCH`

function 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`XMATCH`

function. 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`INDEX`

function. 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.