Lookup & Reference
XMATCH reverse search: find position of LAST occurrence (search_mode = -1)
Locate the bottom-most occurrence of a repeated event, SKU, or customer in a chronological log with XMATCH(Key, Range, 0, -1) and measure first-to-last row span.
Replace clunky LOOKUP(2, 1/(Range=Key)) tricks with native XMATCH(..., 0, -1)!
XMATCH with exact match mode 0 and search mode -1 scans from the bottom of the supplied range. It returns a one-based position within that range, not an absolute worksheet row. Exact matching is case-insensitive and treats wildcard characters literally.
The last row is the latest event only if the source is ordered chronologically. This worksheet has no timestamps and therefore reports source-row positions, not elapsed time or independently verified recency.
Subtract first position from last position for the row-index distance. A single occurrence gives zero. The distance is not the count of matching records and is not an inclusive row count. Missing or blank queries return position zero and a No matches span.
| Position | System Event Log (A2:A7) | Role for Target "Sync" | XMATCH Output |
|---|---|---|---|
| 1 (A2) | Login | — | — |
| 2 (A3) | Sync | FIRST "Sync" (search_mode = 1) | Position = 2 |
| 3–5 (A4:A6) | Export, Sync, Backup | Intermediate activity | — |
| 6 (A7) | Sync | LAST "Sync" (search_mode = -1) | Position = 6 (Span = 6 - 2 = 4) |
Position of LAST occurrence via XMATCH bottom-up search_mode = -1
Third argument 0 enforces exact match; fourth argument -1 searches from last item to first.
=IF(COUNTBLANK(D2)>0,0,IFNA(XMATCH(D2,A2:A7,0,-1),0))Row span between FIRST and LAST occurrence
Use guarded first and last positions before subtraction to distinguish no matches from a single match.
=LET(firstPosition,IF(COUNTBLANK(D2)>0,0,IFNA(XMATCH(D2,A2:A7,0,1),0)),lastPosition,IF(COUNTBLANK(D2)>0,0,IFNA(XMATCH(D2,A2:A7,0,-1),0)),IF(firstPosition=0,"No matches",lastPosition-firstPosition))Try the example
Use the original values or change them to test your understanding.