Excel XMATCH is a lookup function that is used to search for a specified item in a range or an array and returns its relative position. It is an improved version of the MATCH function with additional capabilities and features.
Excel XMATCH Function Summary
The Excel XMATCH function is a more advanced version of the MATCH function that offers additional features and flexibility. With XMATCH, you can perform approximate and exact matching, reverse search, and use wildcards for partial matches. This makes it a powerful tool for finding and retrieving data in Excel.
XMATCH Function Purpose
The purpose of the XMATCH function is to return the relative position of an item in a range or an array based on specified search criteria.
XMATCH Function Arguments
The XMATCH function takes three arguments:
- Lookup value: The value that needs to be searched.
- Lookup array: The range or an array where the lookup value needs to be searched.
- Match type: The type of match, i.e., exact match, exact match or next smallest/largest, etc.
XMATCH Function Return value
XMATCH returns the relative position of the lookup value within the lookup array. The relative position can be an exact match, an exact match or the next smallest/largest value, or an error.
XMATCH Function Syntax
=XMATCH(lookup_value, lookup_array, [match_type], [search_mode])XMATCH Function Options
When using the XMATCH function in Excel, the third and fourth arguments allow you to control the matching mode and search mode, respectively. Here’s a breakdown of what each option does.
XMATCH Match mode
The XMATCH function offers various options to control the matching behavior when searching for a value in an array or range. The third argument for XMATCH is match_mode, which determines how the function matches values.
Here’s a table that summarizes the match_mode options available for XMATCH:
| match_mode | Description |
|---|---|
| 0 (Exact match) | Searches for an exact match to the lookup value. If there are multiple matches, it returns the position of the first one found. |
| -1 (Exact match or next smallest) | Searches for an exact match to the lookup value, or if there is no exact match, it returns the position of the next smallest value in the lookup array. |
| 1 (Exact match or next largest) | Searches for an exact match to the lookup value, or if there is no exact match, it returns the position of the next largest value in the lookup array. |
| 2 (Wildcard match) | Searches for a match to the lookup value using wildcard characters (* or ?) to match any number of characters or a single character, respectively. |
| -2 (Wildcard match or next smallest) | Searches for a match to the lookup value using wildcard characters (* or ?) to match any number of characters or a single character, respectively. If there is no exact or wildcard match, it returns the position of the next smallest value in the lookup array. |
| 3 (Wildcard match or next largest) | Searches for a match to the lookup value using wildcard characters (* or ?) to match any number of characters or a single character, respectively. If there is no exact or wildcard match, it returns the position of the next largest value in the lookup array. |
Note: The default value for match_mode is 0 (Exact match).
XMATCH Search mode
Apart from the match_mode option, the XMATCH function also supports search_mode options to control how it searches for values in an array or range. The fourth argument for XMATCH is search_mode, which specifies whether the function should perform a binary search or a linear search.
Here’s a table that summarizes the search_mode options available for XMATCH:
| search_mode | Description |
|---|---|
| 1 or omitted | Performs an exact match search (default). |
| 2 | Performs an exact match search, but ignores case sensitivity. |
| -1 or -2 | Performs a wildcard search, where ? matches any single character and * matches any sequence of characters. A negative value for search_mode (-1 or -2) indicates that the search will be done from right to left. |
Note: the search_mode argument is optional and if it is omitted, XMATCH will perform an exact match search by default.
XMATCH Examples
XMATCH is a powerful function that can help you search and match data with flexibility and ease. Here are some examples of how XMATCH can be used in practical scenarios:
Exact Match
Suppose you have a list of items and you want to find the position of a specific item in that list. You can use the XMATCH function to do this.
For example, if you want to find the position of “apple” in a list of fruits, you can use the following formula:
=XMATCH("apple", A1:A10, 0)
Here, “apple” is the item you want to find, A1:A10 is the range where you want to search for the item, and 0 indicates an exact match. The function will return the position of the item if it is found, otherwise it will return an error.
Approximate Match
You can also use the XMATCH function to find the position of the closest match to a given value in a list of values.
For example, if you have a list of grades and you want to find the position of the closest grade to 85, you can use the following formula:
=XMATCH(85,A2:A6, 1)
Here, 85 is the value you want to find the closest match for, A2:A6 is the range where you want to search for the value, and 1 indicates an approximate match. The function will return the position of the closest match if it is found, otherwise it will return an error.
Reverse Search
You can also use the XMATCH function to search a range of cells from right to left instead of left to right.
For example, if you have a list of names in a row and you want to find the position of the last occurrence of a specific name, you can use the following formula:
=XMATCH("John", B1:F1, -1)
Here, “John” is the name you want to find, B1:F1 is the range where you want to search for the name, and -1 indicates a reverse search. The function will return the position of the last occurrence of the name if it is found, otherwise it will return an error.
Wildcard Match
You can also use the XMATCH function to search for partial matches using wildcards (* and ?).
For example, if you have a list of URLs and you want to find the position of the URL that contains “google” in it, you can use the following formula:
=XMATCH("google", A1:A10, 0)Here, “google” is the search string with * indicating any number of characters before or after “google”, A1:A10 is the range where you want to search for the URL, and 0 indicates an exact match. The function will return the position of the first URL that contains “google” if it is found, otherwise it will return an error.
By using the XMATCH function with different arguments, you can search for specific items or values in a range of cells in Excel.
XMATCH Function Examples Combined with Other Functions
The XMATCH function in Excel is a powerful tool that can be used in conjunction with other functions to perform complex operations on data. Here are some examples of how XMATCH can be combined with other functions:
Using XMATCH with INDEX to return values from a specific column
Suppose you have a table with multiple columns of data and you want to extract values from a specific column based on a lookup value. You can use the XMATCH function with the INDEX function to accomplish this.
Suppose you want to extract the age of the person whose name is “Jane”. You can use the following formula:
=INDEX(B2:B4,XMATCH("Jane",A2:A4,0))
This formula uses XMATCH to find the position of “Jane” in the range A2:A4 and returns the corresponding value from the range B2:B4 using the INDEX function.
Using XMATCH with IF to perform conditional lookups
You can also use XMATCH with the IF function to perform conditional lookups. For example, our table has a list of products and there price.
Suppose you want to find the price of a product based on its name, but return a default value if the product does not exist in the table. You can use the following formula:
=IF(XMATCH("Pear",A2:A4,0),INDEX(B2:B4,XMATCH("Pear",A2:A4,0)),"Not Found")
This formula uses XMATCH to check if “Pear” exists in the range A2:A4. If it does, it returns the corresponding value from the range B2:B4 using the INDEX function. If it doesn’t, it returns the text “Not Found”.
Using XMATCH with SUMIFS to perform conditional sums
You can also use XMATCH with the SUMIFS function to perform conditional sums. For example, say we have a table with Products, Category’s, and Sales.
Suppose you want to find the total sales of all fruits. You can use the following formula:
=SUMIFS(C2:C6,B2:B6,"Fruit")
This formula uses the SUMIFS function to sum the values in the range C2:C6 based on the criteria that the values in the range B2:B6 are equal to “Fruit”.
However, if you have a large table with many categories, it can be cumbersome to manually type out each category as a criteria in the SUMIFS function. You can use XMATCH to automate this process. For example, suppose you have a cell named “Category” that contains the category you want to sum. You can use the following formula:
=SUMIFS(C2:C6,B2:B6,INDEX(D2:D3,XMATCH(Category,C2:C6,0)))This formula uses XMATCH to find the position of the category in the range C2:C6 and returns the corresponding value from the range D2:D3 using the INDEX function. It then uses this value as the criteria in the SUMIFS function to sum
XMATCH vs. MATCH?
XMATCH and MATCH are two Excel functions used for finding the position of a specified value within a range. The main differences between XMATCH and MATCH are:
- XMATCH is only available in the latest version of Excel (365) and replaces
- MATCH in terms of functionality.
- XMATCH allows you to perform approximate matches, exact matches, and search from the last value.
- MATCH only performs exact matches and searches from the first value.
Here are some bullet points highlighting the main features of each Excel function:
XMATCH:
- Available in Excel 365 and above.
- Can perform approximate matches using match_type argument.
- Can perform exact matches.
- Can search from the last value using the search_mode argument.
MATCH:
- Available in all versions of Excel.
- Only performs exact matches.
- Searches from the first value in the range.
Excel XMATCH Function Notes
- XMATCH is only available in Excel 365 and later versions.
- The match type argument has different options like exact match, exact match or next smallest/largest, etc.
- XMATCH can also perform a binary search on a sorted range for faster results.