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 1st to 9th 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)
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.
Step 2: Choose cell E5, enter the 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.
Step 3: Select cell F4, enter the 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.
Step 2: Choose cell F5, enter the 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.
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.
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