XMATCH Generator
Find the position of a value in a range, with exact, approximate, wildcard, or reverse search.
XMATCH
Find the position of a value in a range, with exact, approximate, wildcard, or reverse search.
Cell or value to locate.
A single column or single row to search.
0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
1 first to last, -1 last to first, 2 binary ascending, -2 binary descending.
=XMATCH(A2, $D$2:$D$100, 0, 1)Worked Example
Product names are listed in D2:D100 and Widget B appears more than once. You want the position of the last occurrence.
=XMATCH("Widget B", $D$2:$D$100, 0, -1)Returns: Searching bottom-up, if the last Widget B sits in D20 the formula returns 19, its position within D2:D100 (D2 is position 1). Wrap it in INDEX to pull the matching value.
Checks Before You Paste
- •XMATCH returns a position number, not a value. Feed it to INDEX - =INDEX($E$2:$E$100, XMATCH(A2, $D$2:$D$100, 0, 1)) - or use two XMATCH calls inside one INDEX for a row-and-column lookup.
- •Search mode -1 walks the list backwards, which is the easy way to grab the LAST matching row (latest transaction, most recent status) without any array gymnastics.
- •Binary search modes 2 and -2 are fast but assume the data is genuinely sorted in that direction. On unsorted data they do not error - they return a wrong position or a stray #N/A, so only use them on data you control.