Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Parameter | Description |
|---|---|
| lookup_value | Parameter of the XMATCH function. |
| lookup_array | Parameter of the XMATCH function. |
| [match_mode] | (Optional.) Parameter of the XMATCH function. |
| [search_mode] | (Optional.) Parameter of the XMATCH function. |
Examples
Find position of a value
Formula
=XMATCH("Gadget", A2:A100)
Returns: 2 — "Gadget" is at position 2 in the range (exact match by default)
Search from the bottom
Formula
=XMATCH("East", A2:A100, 0, -1)
Returns: the position of the LAST occurrence of "East" in the range
Wildcard search
Formula
=XMATCH("Wid*", A2:A100, 2)
Returns: the position of the first entry starting with "Wid" (match_mode 2 enables wildcards)
Common Errors
#N/A
The value was not found in the range. Check spelling, extra spaces, or whether the value exists in the data.
#VALUE!
The search range is not a single row or column. XMATCH requires a one-dimensional range.
Tips
Exact match by default
Unlike MATCH, XMATCH defaults to exact match (match_mode 0). No more forgotten third argument causing approximate matches.
Search direction
search_mode: 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending.
Use with INDEX
=INDEX(C:C, XMATCH(val, A:A)) is the modern equivalent of INDEX-MATCH, with cleaner syntax and better defaults.
Try XMATCH in Viztab
Import your data and use XMATCH with 370+ other formulas. No signup required.
Open Viztab