Excel like a finance pro.
← The libraryPractice · 3 questions →
Lookup function
XMATCH
Returns the position of a value with exact match by default and optional reverse search.
CommonDifficulty 1300 · ProficientUsage rank #71 of 520
When to use it
MATCH with XLOOKUP's options: exact match by default, wildcard and next-larger modes, and searching from the end.
The shape of it
- Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Worked examples
Exact position
=XMATCH("Gizmo",A2:A6) → 3
Exact by default.
Band position
=XMATCH(72,{0,60,70,80,90},-1) → 3
Exact or next smaller.
Search backwards
=XMATCH("Widget",A2:A6,0,-1) → 1
Search from the last row; with duplicates this returns the last occurrence.
Worth knowing
- Count values below a threshold in a sorted list with match mode 1 and binary search mode 2 for speed.
- Feed it into INDEX exactly like MATCH.
- Wildcard mode 2 allows * and ?.
Where it goes wrong
- #N/A when not found.
- Binary search modes need sorted data or return wrong positions.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.