XMATCH

Return where a value sits in a range, with exact matching by default and the option to search backwards.

Syntax

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

Argument	Required	Description
`lookup_value`	Required	The value to find.
`lookup_array`	Required	The range to search.
`match_mode`	Optional	0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
`search_mode`	Optional	1 first to last (default), -1 last to first.

Examples

Every example below was executed against VisiGrid engine 0.35.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=XMATCH("bo",A2:A4)`	`2`	Exact match is the default — no fourth argument to forget.
`=XMATCH("zz",A2:A4)`	`#N/A`
`=XMATCH("b*",A2:A4,2)`	`2`	Match mode 2 turns on wildcards.
`=XMATCH("ops",B2:B4)`	`1`	Forwards finds the first ops.
`=XMATCH("ops",B2:B4,0,-1)`	`3`	Backwards finds the last — but the position is still counted from the top.
`=INDEX(B2:B4,XMATCH("cy",A2:A4))`	`ops`

What it fixes about MATCH

MATCH defaults to approximate matching, which on unsorted data returns a confidently wrong position rather than #N/A. XMATCH defaults to exact. That is the whole reason to prefer it:

=MATCH("bo", A2:A4)        approximate unless you pass 0
=XMATCH("bo", A2:A4)       exact unless you ask otherwise

Everything else is additions rather than changes.

Searching backwards

search_mode of -1 walks the range from the end, which is how you find the most recent matching row in a log or a transaction list:

=XMATCH("ops", B2:B4, 0, -1)   → 3

The detail worth pinning down: the position is still counted from the start of the range. Searching backwards changes which match you find, not how the answer is numbered. So the result feeds straight into INDEX with no adjustment.

Wildcards are a mode, not a default

match_mode of 2 enables ? and *. They are off otherwise, so a literal asterisk in your data behaves normally until you ask for pattern matching. That is the opposite of SEARCH, where wildcards are always on and a tilde turns them off.

With INDEX

INDEX and XMATCH together do what XLOOKUP does in one call, and are worth knowing for the case XLOOKUP does not cover: reusing one position across several result columns.

=INDEX(B2:B4, XMATCH("cy", A2:A4))

Compute the position once, index into as many columns as you like. For a single value, XLOOKUP is shorter and clearer.

Excel compatibility

Matches Excel across all four match modes and both search modes.