XMATCH function
Summary
Returns the relative position of an item in an array or range of cells.
Syntax
The XMATCH function returns the relative position of an item in an array or range of cells.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
lookup_value Required | The lookup value
lookup_array Required | The array or range to search
[match_mode] Optional | Specify the match type: 0 - Exact match (default) -1 - Exact match or next smallest item 1 - Exact match or next largest item 2 - A wildcard match where *, ?, and ~ have special meaning.
[search_mode] Optional | Specify the search type: 1 - Search first-to-last (default) -1 - Search last-to-first (reverse search). 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
lookup_value Required | The lookup value
lookup_array Required | The array or range to search
[match_mode] Optional | Specify the match type: 0 - Exact match (default) -1 - Exact match or next smallest item 1 - Exact match or next largest item 2 - A wildcard match where *, ?, and ~ have special meaning.
[search_mode] Optional | Specify the search type: 1 - Search first-to-last (default) -1 - Search last-to-first (reverse search). 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Example
=XMATCH(F2,C3:C9,1)
=INDEX(C6:E12,XMATCH(B3,B6:B12),XMATCH(C3,C5:E5))
=XMATCH(4,{5,4,3,2,1})
=XMATCH(4.5,{5,4,3,2,1},1) produces 1 in this exact match case, the match_mode argument (1)
=INDEX(C6:E12,XMATCH(B3,B6:B12),XMATCH(C3,C5:E5))
=XMATCH(4,{5,4,3,2,1})
=XMATCH(4.5,{5,4,3,2,1},1) produces 1 in this exact match case, the match_mode argument (1)