XMATCH Function

Finds the position of a value — the modern upgrade to MATCH.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2021+

Returns

A position number

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · INDEX & MATCH module

Overview

XMATCH returns where a value sits in a list, like MATCH, but defaults to an exact match and can search from the bottom up.

Pair it with INDEX for a flexible lookup that works in any direction.

Syntax

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

Arguments

ArgumentRequired?What it does
lookup_valueYesWhat to find.
lookup_arrayYesThe row or column to search.
match_modeOptional0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard.
search_modeOptional1 = first to last (default), -1 = last to first, 2/-2 = binary search.

Examples

Example 1 — Position in a list

Where is Dev?

C2fx=XMATCH("Dev", A2:A5)
ABC
1RepPosition
2Ana4
3Ben
4Cara
5Dev
=XMATCH("Dev", A2:A5) Explain

Example 2 — Two-way lookup with INDEX

Find a rep's sales for a chosen month.

=INDEX(B2:M20, XMATCH(P1, A2:A20), XMATCH(P2, B1:M1)) Explain
Use search_mode -1 to find the last occurrence: =XMATCH("Ana", A2:A500, 0, -1).

Common mistakes

  • #N/A errors. The value isn't in the list — check for spaces or numbers stored as text.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.