What it does – Return the relative position of an item in an array or range that matches a specified value.
Syntax=XMATCH(search_key, lookup_range, [match_mode], [search_mode])search_key – This is the value to search for. This can be either a number, text or cell reference.lookup_range – This argument specifies the range or array where the lookup value is to be searched for. The range must be either a row or a column.match_mode: This parameter specifies the type of value to be looked for. It is an optional argument.search_mode: This argument specifies the direction in which to search for the value. This is also an optional argument.
Sample Usage=XMATCH(“Alice Baker”, A2:A11)//Returns the relative position of Alice Baker in the range A2:A11 from the top.=XMATCH(25000, B2:B11, 1)
//Returns the number of employees with sales greater than 2500 from the range B2:B11.
Note: For match mode and search mode values, see the tables below.
Get the spreadsheet with the sample data and formulas here.
The XMATCH function in Google Sheets searches for a specified item in an array or range of cells, and then returns the relative position of the item in a range that matches a specified value.
The XMATCH function in Google Sheets is similar to the MATCH function but more advanced and supports enhanced match and search functionality. It can look up for matching values horizontally as well as vertically, and find exact or approximate and partial matches.
Excited to learn more about this new and powerful function in Google Sheets? Great!
Let us jump right into it without further ado.
- Syntax of the XMATCH function
- Basic usage
- Using XMATCH for approximate match
- Using XMATCH function for partial match
- Search for matching values from the last
- XMATCH function with INDEX function
How to use the XMATCH function in Google Sheets
Hold on. We have gone a little bit ahead of ourselves. Before we learn How to Use the Google Sheets XMATCH Function, we need to understand the syntax of the function.
Formula of the XMATCH function in Google Sheets
The syntax of the XMATCH function in Google Sheets:
=XMATCH(search_key, lookup_range, [match_mode], [search_mode])A brief explanation of each of the arguments is given below:
- search_key – This is the value to search for. This can be either a number, text or cell reference.
- lookup_range – This argument specifies the range or array where the lookup value is to be searched for. The range must be either a row or a column.
- match_mode: This parameter specifies the type of value to be looked for. It is an optional argument.
It accepts four values:
| 0 | Exact match; this is the default value, and if not specified searches for the exact match |
| 1 | Exact match or the next greater value |
| -1 | Exact match or the next smaller value |
| 2 | Wildcard match (*, ?) |
- search_mode: This argument specifies the direction in which to search for the value. This is also an optional argument.
| 1 | Search from first to last – default |
| -1 | Search from last to first |
| 2 | Binary search – ascending order range |
| -2 | Binary search – descending order of range |
Now that we’ve understood the syntax and the arguments, let us now learn how to use the XMATCH function in Google Sheets.
Using the XMATCH Function in Google Sheets
Let us consider the following data set, a list of employees sorted in descending order of their sales, for our example.
Make a copy of the spreadsheet used in this tutorial and follow along with the tutorial. It contains the data as well as all the formulas used. Click here to get a copy.
What we are going to do is to find the relative position of an employee, say Alice Baker, based on their sales figure using the XMATCH function in Google Sheets.
To so so, we can specify the name of the employee as the search value and the list of the names and the lookup range. We can leave the other arguments out.
So the XMATCH formula will be as shown below:
=XMATCH(“Alice Baker”,A2:A11)This will give us the relative position of Alice Baker, which is 6, as shown.
We can make the formula more flexible and versatile by referencing a cell instead of specifying the search key as text.
So, for example, we can replace “Alice Baker” in the above formula with E2. We can then enter the name of any employee in cell E2 and get their relative position.
=XMATCH(E2, A2:A11)Entering James Clark returns his position on the table. We can type in any name and it will give us the position of that particular employee.
Note: The XMATCH function is case insensitive. Alice bAKER will return the same result as Alice Baker.
Using the XMATCH function in Google Sheets for approximate match
We can use the XMATCH function in Google Sheets to lookup for an approximate match instead of the exact match. For this we need to specify the match mode.
Let us have a look at the different match modes again.
0 = exact match, 1 = exact match or the next greatest value, -1 = exact match or the next smallest match, 2 = partial match
Let us use the same data set above and consider an example–this time to check the number of employees with sales greater than 25000 pounds. For this the match mode will be 1, as we want to look up for value 25000 or the next greatest value.
The formula is as follow:
=XMATCH(25000,B2:B11,1)This will give us the number of employees with sales greater than or equal to 25000 pounds–which is 7.
How to Use the XMATCH function in Google Sheets for partial match
We can use the XMATCH function in Google Sheets to lookup for a partial match by using wildcard characters, * and ?.
Let us use the same dataset again and consider another example.
This time we’ll look up the relative position of an employee, say Peter Shepehd, by using only one of their names of a part of their name.
To find the relative position of Peter Shepherd using only his first name, we can use the following formula:
=XMATCH(“Peter*”, A2:A11, 2)Here, the third argument, 2, is to indicate that we are looking for a partial match and not the exact match.
We can also use a cell reference to search a for partial match. To reference a cell, we just need to replace the search key with the cell. Typing the name–full or part of it–will return the position of that person.
We must note here that we need to specify the search that it is a partial match by adding * at the end of the search key.
The formula, using cell H3 as the reference cell, is:
=XMATCH(H3, A2:A11, 2)
Search for a match starting from the last using the XMATCH function in Google Sheets
By default, the XMATCH function searches for values starting from the top. We can modify the way the XMATCH function in Google Sheets looks for values by specifying the search mode.
To look up from the last value, we use -1 as the argument for the search mode.
Consider the modified table shown below.
Here, Harry Smith appears two times. Unless we specifically tell the formula to look up from the last value, the formula will return the position of the first from the list.
The formula for reverse lookup with the XMATCH function in Google Sheets to find the last match is as follows:
=XMATCH(“Harry Smith”, A2:A11, 0, -1)This will return the position of Harry Smith, as shown in the following image.
XMATCH function in Google Sheets with INDEX function
The XMATCH function in Google Sheets can be used with the INDEX function. The function is similar to how we use the INDEX MATCH function.
What the INDEX XMATCH function does is look up the position of the search key and return the contents of the cell at the intersection of the specified row and column.
Let us modify the original dataset by adding a position column and use the INDEX XMATCH function to fetch the position of an employee.
Using cell F3 as the reference cell, the formula for the INDEX XMATCH function will be as shown under:
=INDEX(A2:A13,XMATCH(F3,B2:B13))Entering the name of an employee in reference cell will return the position of that particular person.
We can use the INDEX XMATCH function to fetch the sales figures instead of the position. We only need to change the reference range of INDEX function to that of the column containing the sales values.
The formula will be as given below:
=INDEX(C2:C13,XMATCH(I3,B2:B13))Upon entering the employee name in the cell I3, we’ll get the sales figure of the employee, as shown below.
Conclusion
The XMATCH function in Google Sheets, a more flexible version of the MATCH function, is a useful function and can be used for several different purposes, a few of which we have learned. There are many more things we can do with the XMATCH function in Google Sheets but the examples we have considered will suffice for the introduction to the XMATCH function.
For more advanced functions in Google Sheets–as well as simple ones–you can visit our blog. It’s your gateway to mastering Google Sheets.
See also
Here are some related tutorials you may find useful:
https://blog.tryamigo.com/how-to-use-xlookup-function-in-google-sheets/
https://blog.tryamigo.com/vlookup-and-hlookup-together-in-google-sheets/
https://blog.tryamigo.com/vlookup-from-another-sheet-in-google-sheets/