The XMATCH function in Excel is a powerful tool that helps you find the relative position of an item in a range or array. It is an enhanced version of the MATCH function, providing additional functionality and flexibility. Here’s how you can use the XMATCH function:
Syntax:
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
- `lookup_value`: The value you want to search for in the `lookup_array`.
- `lookup_array`: The range or array of data where you want to search for the `lookup_value`.
- `[match_mode]` (optional): Specifies the match type.
- `0`: Exact match (default).
- `-1`: Exact match or next smallest item.
- `1`: Exact match or next largest item.
- `2`: Wildcard match where `*` matches any sequence of characters and `?` matches any single character.
- `[search_mode]` (optional): Specifies the search mode.
- `1`: Search from first to last (default).
- `-1`: Search from last to first.
- `2`: Binary search in ascending order.
- `-2`: Binary search in descending order.
Basic Example:
Suppose you have a list of names in cells A1:A5, and you want to find the position of “John”.
=XMATCH("John", A1:A5)
Example with Match Mode:
If you want to find the position of an approximate match or next largest within a numeric list, you can use the match mode option.
=XMATCH(25, B1:B10, 1)
Example with Search Mode:
If you want to search from the last item in the list upwards:
=XMATCH("Smith", A1:A10, 0, -1)
Wildcard Example:
You can also use wildcards to match patterns. For example, to find a name starting with “J” and ending in “n”:
=XMATCH("J*n", A1:A5, 2)
Tips:
- Combine XMATCH with INDEX if you need to return the value at a specific position.
- It is particularly useful for dynamic array operations and cases where you need more condition matching than the traditional MATCH function offers.
By utilizing XMATCH, you can improve your lookup capabilities significantly, especially in cases requiring more complex search conditions or patterns.
Unlock Your Potential
Excel
Access
Power BI
