Summary
The Excel XMATCH function is designed to perform lookups and return the position of a value within a range. It serves as a more robust and flexible alternative to the older MATCH function. XMATCH supports approximate and exact matching, reverse searches, and wildcards (* ?) for partial matches, making it an essential tool for advanced lookup operations.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
- lookup_value: The value to search for
- lookup_array: The array or range in which to search
- [match_mode]: [Optional] 0 for exact match (default), -1 for exact match or next smallest, 1 for exact match or next larger, 2 for wildcard match
- [search_mode]: [Optional] 1 for search from first (default), -1 for search from last, 2 for binary search ascending, -2 for binary search descending
Return value
The numeric position of the lookup value in the lookup array.
How to use
XMATCH is used for locating the position of a specific value in a range or array. It is versatile in handling various types of lookups, including vertical and horizontal ranges, and supports different matching and search modes.
Examples
Simple XMATCH
Finding Position of a Value in a Column: To find the position of a specific item in a column:
=XMATCH("Mars", B5:B13)
This formula searches for “Mars” in the range B5:B13 and returns its position.
XMATCH for Approximate Match
Performing Approximate Match Lookups: Finding the closest match to a given value in a range:
=XMATCH(50, B5:B9, 1)
This formula finds the closest match to the number 50 in B5:B9 and returns the position of the closest larger value.
XMATCH for Wildcard Match
Using Wildcards for Partial Matches: To find the position of a value containing a specific substring:
=XMATCH("*apple*", B5:B13, 2)
Uses wildcards to find a partial match of “apple” in B5:B13 and returns its position.
XMATCH for Reverse Search
Performing a Reverse Search: To search from the end of the array to the start:
=XMATCH("Mars", B5:B13, 0, -1)
This formula searches for “Mars” in B5:B13 starting from the last value and moving towards the first.
XMATCH with Complex Criteria
Applying Multiple Conditions in a Lookup: Combining XMATCH with Boolean logic for multiple criteria:
=XMATCH(1, (B5:B13="Mars")*(C5:C13>100), 0)
This formula finds the first position where “Mars” appears in B5:B13 and its corresponding value in C5:C13 is greater than 100.
Additional Notes
- XMATCH is more powerful and flexible than the traditional MATCH function, especially useful in complex data analysis.
- The function is dynamic, updating automatically with changes in source data.
- XMATCH provides additional capabilities such as reverse searches and binary searches, enhancing the efficiency of lookups.