XMATCH Function In Google Sheets: The Ultimate Guide

XMATCH Function In Google Sheets: The Ultimate Guide
Reading Time: 7 minutes

Quick guide
How to use the XMATCH function in Google Sheets:
↠ Enter the function name =XMATCH
↠ Select the cell that contains the value, eg. A5
↠ Select the range that contains the search value, eg. A2:A10
↠ The XMATCH formula then becomes =XMATCH(A5, A2:A10)
This returns the position of A5, which is 4

XMATCH function in Google Sheets

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.

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 XMATCH function in Google Sheets, 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:

0Exact match; this is the default value, and if not specified searches for the exact match
1Exact match or the next greater value
-1Exact match or the next smaller value
2Wildcard match (*, ?)
  • search_mode: This argument specifies the direction in which to search for the value. This is also an optional argument.
1Search from first to last – default
-1Search from last to first
2Binary search – ascending order range
-2Binary 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.

Sample data for the XMATCH function

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.

XMATCH function in Google Sheets with text as search value

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.

XMATCH in Google Sheets with cell reference

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.

XMATCH function to return the numbe of employees with sales greater than 25k pound

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

XMATCH in Google Sheets for partial match using wildcard

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)
Partial match using cell reference

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.

Modified table for reverse lookup

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 from the bottom value

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.

Table modified for INDEX XMATCH

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.

Using INDEX XMATCH to return the position

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.

Using INDEX XMATCH to return sales figures

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/