Returns the relative position of an item in an array or range of cells
Master the fundamentals of Excel XMATCH function
The XMATCH function is a modern replacement for MATCH. It provides more flexibility and better performance for finding positions in arrays.
XMATCH is the successor to MATCH, offering more flexibility and better performance
XMATCH supports exact matches, approximate matches, and wildcard matches
XMATCH can search from first to last, last to first, or use binary search for sorted data
Function-specific parameters
Function-specific return type
XMATCH is the successor to MATCH, offering more flexibility and better performance
XMATCH supports exact matches, approximate matches, and wildcard matches
XMATCH can search from first to last, last to first, or use binary search for sorted data
Exact matching required
Returns numeric position
Handles missing text gracefully
=XMATCH(lookup_value, lookup_array, match_mode, search_mode)The value to search for
The array or range to search in
Optional. 0 (exact match), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard match)
Optional. 1 (first to last), -1 (last to first), 2 (binary ascending), -2 (binary descending)
The relative position of the lookup_value in the lookup_array
Description: XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Basic XMATCH function
Finds the position of "Apple" in A1:A5
VBA implementation for XMATCH.
Sub XMATCHExample()
Dim result As Variant
result = Application.WorksheetFunction.XMatch("Apple", Range("A1:A5"))
MsgBox "XMATCH result: " & result
End SubPosition finding
Array indexing
Data matching
Lookup operations
Dynamic positioning
XMATCH not working
Solution: Ensure the lookup_value exists in the lookup_array and check the match_mode parameter
Unexpected results
Solution: Check the search_mode parameter for the correct search direction