Home › Functions › Lookup & Reference › XMATCH
fx
XMATCH
Lookup & Reference  ·  Since: Microsoft 365  ·  9 languages

The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.

🌐 Translations

English XMATCH
Deutsch XVERGLEICH
Français EQUIVX
Español COINCIDIRX
Italiano CONFRONTA.X
Português CORRESPX
Nederlands X.VERGELIJKEN
Polski X.DOPASUJ
Русский ПОИСКПОЗX

📦 Availability

Excel versionAvailable
Excel 2007 ✗
Excel 2013 ✗
Excel 2019 ✗
Excel 2021 ✗
Microsoft 365 ✓
Translate a formula with this function
fx EN
fx DE

⌨ Syntax

XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

Arguments

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.

▶ Examples

=XMATCH(F2,C3:C9,1)
=XMATCH(4,{5,4,3,2,1})
=XMATCH(4.5,{5,4,3,2,1},1)

Related functions

Copied to clipboard