XMATCH
Returns the relative position of a value in an array, with flexible match modes.
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Arguments
lookup_valueValue to findlookup_arrayArray to searchmatch_mode0=exact, -1=exact or smaller, 1=exact or larger, 2=wildcard(optional)search_mode1=first, -1=last, 2=binary asc, -2=binary desc(optional)
=XMATCH("Apple",A:A)Find exact position
=XMATCH(50,B:B,1)Finds 50 or next larger
=XMATCH("*berry",A:A,2)Finds first ending in berry
- •Modern replacement for MATCH
- •Supports wildcards with match_mode 2
- •Can search from last to first
- •Forgetting that XMATCH's default match_mode is exact match, unlike the older MATCH function, whose default is actually the less-intuitive approximate match - assuming they behave the same by default leads to different results
- •Not using the search_mode argument when searching from the end of a list makes more sense, missing out on XMATCH's ability to search in reverse or via binary search on sorted data
- •Using XMATCH alone expecting it to return the matched value - like MATCH, it only returns a position, and needs to be wrapped in INDEX to actually retrieve a value
MATCHThe older, more limited version of XMATCH, whose default approximate-match behavior differs from XMATCH's default exact match.INDEXAlmost always paired with XMATCH, since XMATCH alone only returns a position, not the actual value at that position.XLOOKUPCombines the lookup-and-retrieve logic of INDEX/XMATCH into a single function, often preferred when you don't need XMATCH's position output specifically.What's the difference between MATCH and XMATCH?
XMATCH defaults to an exact match, while MATCH defaults to an approximate match requiring sorted data - a key difference that catches people who assume they behave the same way. XMATCH also adds search direction and binary-search options MATCH doesn't have.
Does XMATCH return the matched value?
No, it returns the position of the match within the array, not the value itself. Wrap it in INDEX, like INDEX(range,XMATCH(...)), to retrieve the actual value.
When should I use XMATCH instead of XLOOKUP?
Use XMATCH when you specifically need the position of a match - for example, to feed into INDEX yourself or use in another calculation. XLOOKUP is more direct when you just want the corresponding value.
Need to translate a formula using XMATCH?
Use our translator to convert your complete formula