## What Is **XMATCH Function In Google Sheets**?

The XMATCH in Google Sheets is a built-in Lookup function. It takes a search value, lookup array, and the applicable match and search modes as its input values. The function then searches for the cited value in the given array range to return the value’s relative position.

Users can make use of the XMATCH function in Google Sheets for finding an exact, approximate, or partial match, in the desired order of financial and statistical data.

For instance, the following dataset lists US tech companies and their revenues in USD.

We are required to determine the position of the company **Meta** in column A of the source dataset and showcase the output in cell B9.

Then, we can apply the **XMATCH()** in the specified cell to fetch the anticipated result, in line with the meaning of **XMATCH in Google Sheets explained **previously.

The **XMATCH() **takes four input values. The first is the cell reference to the value **Meta**, which we must search or look up to determine its position. Next, the second is the look-up range A2:A6, where we must search the specified lookup value. After that, the third and fourth argument values are **0 **and **1**. The values indicate that the function must find an exact match using the first-to-last search format to find the look-up value’s position in the lookup range.

Thus, for the specified inputs, the **XMATCH in Google Sheets returns** **4** as the **Meta**’s position in the tech companies listed in the source dataset.

##### Table of contents

###### Key Takeaways

- The
**XMATCH**in Google Sheets helps in locating a specified value in a cell range. It then gives the value’s relative position in the cited range. - The
**XMATCH**function in Google Sheets is useful for comparing two lists of data for matches and searching for a value in the reverse order. - The
**XMATCH**function takes two compulsory arguments,**search_key**and**lookup_range**, and two optional arguments,**match_mode**and**search_mode**,as its input. - We can apply the
**XMATCH**function in Google Sheets as a standalone function directly or from the**Insert**tab in a cell. However, using the function with other inbuilt functions, such as**INDEX**and**IF**, makes it more practical.

### Syntax

The **XMATCH **function in Google Sheets syntax is the following:

Where,

**search_key**: The value for which we aim to determine its relative place in a given data range.**lookup_range**: The array in which the**XMATCH()**must search for the**search_key**value.**match_mode**: The argument value indicating the match type.

4. **search_mode**: The argument value indicating the search type.

It is mandatory to supply the first two argument values when **using XMATCH in Google Sheets**, while the last two arguments are optional.

### How To Use XMATCH Function In Google Sheets?

We can use the **XMATCH** function in Google Sheets in the following two ways:

**Access the function from the ribbon.****Enter the function into the sheet manually.**

#### Method #1 – Access The Function From The Ribbon

Select a cell for displaying the output value à The **Insert** tab à The **Function **optionright arrow à The **Lookup **function group right arrow à The **XMATCH **function.

The chosen function gets inserted in the cited cell, with the cursor inside the function brackets. It enables us to update the **XMATCH in Google Sheets arguments **within the brackets.

Please note that we can click the ‘**?**’ symbol next to the function name to view its syntax.

Further, clicking the down arrow in the syntax window will show the function explanation with an example.

Finally, once we enter all the required **XMATCH in Google Sheets arguments**, separated by commas, press **Enter** to execute the function and view its output.

#### Method #2 – Enter The Function Into The Sheet Manually

- Click the cell where we aim to show the outcome.
- Type
**=xmatch(**in the cell. [Alternatively, type**=x**or**=xm**and click the function name from the suggestions to choose the function.] - Provide the argument values, separated by commas, and close the brackets.
- Press
**Enter**to fetch the value the function**XMATCH in Google Sheets returns**.

### Examples

We shall see the practical ways of **using XMATCH in Google Sheets** with illustrations.

#### Example #1 – XMATCH With Wildcard

The source dataset ranks pharma companies based on their market cap in millions of USD.

We need to find the rank of the pharma company whose name starts with the character “**Am**” and showcase the output in cell F3.

In such a case, we can apply the **XMATCH() **in the target cell, considering the syntax of **XMATCH In Google Sheets explained** earlier.

**Step 1: **Click cell F3 to choose it, enter the **XMATCH()**, and press **Enter**.

*=XMATCH(F2,B2:B11,2**)*

[ Alternatively, choose the required cell and then **Insert **à **Function **à **Lookup **à **XMATCH**.

The chosen function formula appears in the required cell.

Next, update the function arguments, separated by commas, inside the function brackets.

Finally, press **Enter** to implement the function in the target cell.]

The **XMATCH()** accepts three arguments as the input.

The first argument is the reference to the lookup value, “**Am***” and the second is the lookup range B2:B11.

In this example, the pharma company name starts with the characters “**Am**”, followed by one or more characters. So, we use the wildcard character ‘*****’, representing the unknown multiple characters. On the other hand, if we knew that only a single character would follow the specified characters, we would use the ‘**?**’. Further, if the cited characters included ‘*****’ or ‘**?**’, we would have to place ‘**~**’ before the specific wildcard character to nullify the wildcard character’s effect.

Next, the third argument value is **2**, indicating the matching mode is the Wildcard match. However, we omit the fourth argument as we want the function to take the default search mode, which is first to last.

So, the function searches for the pharma company name starting with the character “**Am**” in the search range from top to bottom. Since the sixth pharma company name is a partial match, the function returns **6** as the required rank.

#### Example #2 – INDEX And XMATCH

The source dataset includes an item list, the representatives handling their orders, and the items’ order delivery statuses and costs.

The task is to find the item that meets the specified conditions. The item selection conditions are the representative should be David, the order delivery status should be complete, and the order cost must exceed or equal $500. We will take cell G7 as the target cell.

Then, we can apply the **XMATCH()** with the **INDEX **function in the specified cell to secure the required item.

**Step 1: **Select cell G7, enter the **INDEX()** containing the **XMATCH()**, and press **Enter**.

*=INDEX(A2:D11,XMATCH(1,(B2:B11=G3)*(C2:C11=G4)*(D2:D11>=500),0,1),1)*

First, the **XMATCH()** takes four argument values. The lookup value is **1**. Then lookup array contains the product of three condition checks to determine the row in the specified ranges where all the criteria hold, leading to the product value being 1.

In this scenario, all the conditions are true in row 7 of the specified ranges. Thus, the **INDEX()** returns the value in the cell where row 7 and column 1 of the specified range of A2:D11 intersect, which is the cell A8 value, **ATT_107**.

#### Example #3 – XMATCH Reverse Search

We have the entry details of a list of employees from 1^{st} to 9^{th} April 2024.

We need the last entry number of the employee Cheryl Parker, and we must display the value in cell G3.

Then, we can utilize the **XMATCH()** in the target cell, with the search being in the reverse order, to fetch the required data.

**Step 1: **Select cell G3, enter the **XMATCH()**, and press **Enter**.

*=XMATCH(G2,C2:C11,0,-1)*

The **XMATCH()** accepts the cell reference G2 to the lookup value of **Cheryl Parker** as the first input, and it takes the range C2:C11 as the lookup range.

Next, we require an exact match. So, the third argument value is **0**. Finally, since we must search the lookup value in the search range from the bottom to top order, the fourth argument value is **-1**.

Thus, the function searches the lookup value in the lookup range for a match in the reverse order. Since the function finds the first match in row 9 of the specified search range, it returns the value of **9** as the specified employee’s last entry number.

#### Example #4 – Compare Two Columns For Match

We have two columns of data. While the first holds the top richest US states list, the second shows the list of the most expensive US States to reside in.

The requirement is to determine the most expensive US states that are present in the list of the richest US states. We shall use the range C2:C11 to display the matches.

Then, we can secure the desired results using the **XMATCH()** with the **IF** function** **and **ISNA** function in the target cells.

**Step 1: **Choose the target cell C2, enter the below expression, and press **Enter**.

*=IF(ISNA(XMATCH($B$2:$B$11,$A$2:$A$11)),”No”,”Yes”)*

**Step 2: **Utilizing the fill handle, execute the formula in the remaining target cells.

First, the **XMATCH() **looks for a value from the second list within the first list. If a match is identified, its relative place is obtained. Otherwise, the return value is a **#N/A** error. In this scenario, the **XMATCH()** output is the following:

**{4;5;3;#N/A;#N/A;2;#N/A;6;7;#N/A}**

The **XMATCH() **array output is the **ISNA() **input. If the value is the **#N/A** error, the **ISNA()** output is **TRUE**, else **FALSE**. So, its output is the array of logical values mentioned below:

**{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}**

Next, the **IF()** checks the array mentioned above. For the **TRUE **values, the **IF()** output is the TRUE value, “**No**“. Otherwise, its output is the FALSE value of “**Yes**”. So, accordingly, for the states in the second list, which are also in the first list, the formula output is “**Yes**”. Otherwise, the formula returns “**No**”.

### Important Things To Note

- The
**XMATCH**in Google Sheets will return the first match when the lookup array range contains multiple instances of the search value. - The
**XMATCH**function in Google Sheets is case-insensitive. - The lookup array range must be a single row or column.
- When the
**XMATCH**function in Google Sheets does not find the search value in the lookup array range, the function output is the**#N/A**error value.

### Frequently Asked Questions (FAQs)

**1. What is the difference between MATCH and XMATCH in Google Sheets?**

The difference between **MATCH **and **XMATCH **in Google Sheets is as follows:**Different Default Matching Behavior**

The **MATCH()**’sdefault match type is **1**, which locates the largest value below or equal to the search value.

The **XMATCH()**’s default match mode is **0**, which finds the exact match.**Different Behavior For An Approximate Match**

When the match mode or type is **1**, the **MATCH() **identifies the largest value below or equal to the search value. Also, the function requires that the lookup range values should be in ascending order.

However, the **XMATCH()** looks for the exact match or the subsequent largest value than the search value without the need for sorted data.

When the match mode or type is **-1**, the **MATCH()** looks for an exact match or the subsequent larger value than the lookup value. Also, the function requires that the lookup range values should be in descending order.

On the other hand, the **XMATCH()** finds an exact match or the ensuing smaller value than the search value without the need for sorted data.**Partial Match**

The **MATCH()** does not have a match type to utilize wildcard characters to find a partial match. On the other hand, the **XMATCH()** offers the match mode value of **2** to find a partial match using the wildcard characters.**Search Mode**

The **XMATCH()** offers search modes, which enable us to set the search order. However, the **MATCH()** does not provide such an argument value.

For example, we have two datasets containing date-wise inventory data. While the first dataset holds unsorted data, the second had the data sorted in descending order of the inventory levels.

The aim is to determine an approximate match for the inventory level of 1500, specified in cell E1.

We shall see how the **MATCH **and **XMATCH **functions work in the case of the unsorted and sorted data, with the match type or mode being **-1**. Assume the target cells are E4:F5.** Step 1: **Choose cell E4, enter the

**MATCH()**, and press

**Enter**.

*=MATCH(E1,B3:B7,-1)*The

**MATCH()**should return the position of the smallest value that is exceeding or equal to the search value. In this case, the function should return the position of the inventory level value of

**2000**. However, the function output is incorrect, with the reason being that the search array is not sorted in descending order.

**Choose cell E5, enter the**

__Step 2__:**XMATCH()**, and press

**Enter**.

*=XMATCH(E1,B3:B7,-1)*The

**XMATCH()**should return the position of the exact match or the next lowest value than the search value. In this case, the function should return the position of the inventory level value of

**1400**, which is

**3**. So, the function works correctly, even if the search range is not sorted.

**Select cell F4, enter the**

__Step 3__:**MATCH()**, and press

**Enter**.

*=MATCH(E1,B11:B15,-1)*In the case of the second dataset, the search array values are sorted in descending order. So, the

**MATCH()**works properly, returning the correct position of the inventory level of

**2000**, which is

**3**.

**Choose cell F5, enter the**

__Step 2__:**XMATCH()**, and press

**Enter**.

*=XMATCH(E1,B11:B15,-1)*In the case of the second dataset also, the

**XMATCH()**returns the correct position of the inventory level of

**1400**, which is

**4**.

**2. When is the XMATCH in Google Sheets used?**

The **XMATCH** in Google Sheets is used when we need to look up a value for its relative position in a data range. The function enables us to set the desired matching mode, which can be exact, approximate, or partial. Also, it allows us to apply the required search mode, whether top to bottom or vice versa or binary search.

**3. When and how does XMATCH in Google Sheets perform an exact match?**

The **XMATCH** in Google Sheets performs an exact match when we must find the cited lookup value as is in the search range to find the value’s relative position in the range.

In such a case, we must supply **0 **as the third argument, **match_mode**, value to the **XMATCH()** or ignore the argument value as it is the argument’s default value. It enables the function to conduct an exact match.

### Download Template

This article must be helpful to understand **XMATCH In Google Sheets**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

Guide to What Is XMATCH In Google Sheets. Here we explain how to use XMATCH function in Google Sheets with examples and points to remember. You can learn more from the following articles. –

## Leave a Reply