How to Use XMATCH in Excel

XMATCH returns the position of a value inside a range. It is the modern replacement for MATCH and is especially useful when you want cleaner exact matches, reverse search, or a lookup pattern that pairs well with INDEX.

That makes it useful in trackers, dashboards, and summary sheets where the formula needs to find a column or row position before returning the actual value.

The syntax

• lookup_value — the value you want to find
• lookup_array — the range Excel should search
• [match_mode] — optional; 0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard
• [search_mode] — optional; 1 first to last, -1 last to first

For most uses, exact match is the safest pattern:

Basic example with INDEX

If row 1 contains project summary headers and row 2 contains the values, you can return the value under a specific header like this:

XMATCH finds the position of Owner in the header row, and INDEX returns the value from that same position in row 2.

That is a flexible pattern in project summary blocks where the column order may shift over time.

Search from last to first

One of XMATCH's useful upgrades is reverse search.

If column C contains task status and you want the position of the last Done entry:

This can be useful when you want the most recent matching milestone instead of the first one.

XMATCH vs MATCH

MATCH still works. XMATCH is usually easier to reason about in newer Excel environments.

When XMATCH is better than XLOOKUP

Use XLOOKUP when you want the returned value directly.

Use XMATCH when you specifically need the position first, often because:

• you are pairing it with INDEX
• you are locating a dynamic column
• you need the last matching position

That is why XMATCH fits better in some dashboard and tracker formulas than XLOOKUP does.

Common XMATCH mistakes

Where XMATCH fits best

XMATCH is strongest in workbooks where the sheet structure changes over time but the formula still needs to find the right field reliably.

That is common in:

• project trackers
• Gantt charts
• OKR trackers

The Griddy way

XMATCH is not hard once you understand it, but remembering when you want a position versus a returned value is where people usually get tripped up.

"Find the position of the Blocked column in this tracker header, then return the matching value from the summary row"

Griddy can structure the INDEX + XMATCH pattern around your real headers without making you debug the lookup logic manually.

Sources

• Microsoft Support: XMATCH function
• Microsoft Support: MATCH function