Excel XMATCH function: two-dimensional lookup guide

A price list with twelve months across the top and forty products down the side is easy to read and awful to look up. VLOOKUP can only search the leftmost column and return something to its right; INDEX and MATCH work fine but read like assembly. XMATCH cleans up the second half of that pair and lets you build a lookup that reads a row and a column in one formula — the way most business data actually sits on a sheet. If you have Excel 2021 or Microsoft 365, XMATCH is already installed. What follows is the shape of the formula, the two places it usually breaks, and what to reach for when it isn’t available yet.

What XMATCH does that MATCH doesn’t

XMATCH returns the position of a value inside a range — the same job MATCH has done for twenty years. The differences show up in the defaults and in the arguments the two functions accept.

MATCH defaults to an approximate match and expects the lookup array to be sorted ascending. Miss either detail and it returns a wrong-but-plausible number rather than an error, which is why a broken MATCH formula is often invisible until someone spot-checks a row. XMATCH defaults to exact match. That one change removes the most common silent bug in old Excel lookups.

XMATCH also adds a search_mode argument. Set it to -1 and the search runs from bottom to top — useful when the same key repeats and you want the most recent entry. Wildcards move from “hope it works” to a real, opt-in match_mode of 2. Per Microsoft’s function reference, the full signature is:

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

match_mode accepts 0, -1, 1, or 2. search_mode accepts 1, -1, 2, or -2 (the last two are binary searches on pre-sorted data). Both optional arguments default to sane values, so you can ignore them until you need them.

The two-dimensional lookup, one formula

The classic case is a matrix: product names down column A, month headers across row 1, and a grid of prices in between. You want the price of a given product in a given month. The trick is to hand INDEX two positions instead of one — a row and a column — and let each XMATCH find its axis independently.

With the product name in G2 and the month name in G3, the formula is:

=INDEX(B2:D4, XMATCH(G2, A2:A4), XMATCH(G3, B1:D1))

B2:D4 is the price grid — no headers, just the numbers you want back. XMATCH(G2, A2:A4) returns which row of that grid the product sits on. XMATCH(G3, B1:D1) returns which column matches the month. INDEX intersects them. There’s no fourth argument to remember and no FALSE to forget, because both XMATCH calls default to exact match.

The one habit worth building early: keep the two lookup ranges outside the INDEX rectangle. Overlapping them is the fastest way to end up with off-by-one positions the next time somebody rearranges the sheet.

When the column header is a number or a date

This is the failure mode that catches almost everyone the first time they build a 2-D lookup against year columns or a monthly date header. The fastest fix is to coerce the lookup value to the type of the header row, rather than reformat the header row itself. If the header is numeric and the lookup cell holds text:

=INDEX(grid, XMATCH(G2,rows), XMATCH(G3,years))

=INDEX(grid, XMATCH(G2,rows), XMATCH(N(G3),years))

For real dates in the header row, DATEVALUE(G3) does the same job. When the header row is text (apostrophe-quoted numbers pasted from another workbook), invert the fix with TEXT(G3, "0") on the lookup side.

The other quiet culprit: a header row pasted from an export that picked up trailing spaces. XMATCH is not tolerant of whitespace. Wrap the lookup argument in TRIM() and it clears the last handful of these cases:

=INDEX(B2:D4, XMATCH(G2, A2:A4), XMATCH(TRIM(G3), B1:D1))

Anchor it to a Table so column inserts don’t break it

The version above shatters the moment someone inserts a new month column between Feb and Mar. The B2:D4 reference doesn’t move — it now points at the wrong slice of the grid, and the formula quietly returns numbers from the wrong month. Formatting the range as an Excel Table (Ctrl+T) trades those fixed rectangles for structured references that grow and shrink with the data.

1. Select the whole range, headers included.
2. Press Ctrl+T and confirm that the first row is a header row.
3. Name the table from the Table Design tab — call it Prices.

With that in place, the same formula reads:

=INDEX(Prices, XMATCH(G2, Prices[Product]), XMATCH(G3, Prices[#Headers]))

Prices[#Headers] is the header row of the Table; insert a column and it grows automatically. Prices[Product] is the product column; add or delete rows and the formula follows. The lookup keeps working through the kind of casual edits that would silently corrupt a fixed-range version.

Return an entire row or column at the intersection

Pass 0 as one of INDEX’s arguments and it spills the whole other axis. This is how you turn a two-dimensional lookup into a “give me every month for this product” or “give me every product for this month” formula, without changing anything else about the shape:

=INDEX(Prices, XMATCH(G2, Prices[Product]), 0)

That returns the whole matching row as a spilled array — every month across, in one call. To spill down the matching column instead, put the 0 in the row argument:

=INDEX(Prices, 0, XMATCH(G3, Prices[#Headers]))

The same “return a range, not a scalar” idea underpins Excel’s dynamic-array functions in general — for filtering rows by criteria in one call, see the Excel FILTER function walkthrough.

Wildcards, reverse search, and a fallback for older Excel

Three switches worth knowing before this formula shape becomes a habit.

For partial matches, set match_mode to 2. The * matches any character sequence, ? matches a single character, and ~ escapes either:

=INDEX(Prices, XMATCH("Widget*", Prices[Product], 2), XMATCH(G3, Prices[#Headers]))

Wildcards only work on text values; a numeric column will never match a wildcard pattern, even when the pattern looks like a number.

To find the last matching row instead of the first — useful when the same product ID repeats and the newest entry is what you want — set search_mode to -1. The 0 in the third slot preserves exact match:

=INDEX(Prices, XMATCH(G2, Prices[Product], 0, -1), XMATCH(G3, Prices[#Headers]))

And if you’re stuck on Excel 2019 or Excel for Mac 2019, XMATCH isn’t there yet. The direct swap is INDEX with two MATCH calls, each carrying an explicit exact-match argument:

=INDEX(Prices, MATCH(G2, Prices[Product], 0), MATCH(G3, Prices[#Headers], 0))

Same shape, four more characters per formula. The trailing 0 is not optional. Omit it and MATCH falls back to approximate mode on unsorted data and returns the wrong row silently. Our earlier post on when to reach for INDEX MATCH over VLOOKUP covers the pre-XMATCH pattern in more depth. Once you’re on Microsoft 365, XLOOKUP collapses the one-dimensional half of this into a single function.

Two XMATCH calls inside INDEX give you a lookup that reads a matrix by both axes, refuses to invent approximate matches, and — once anchored to a Table — survives the edits the next person makes to the file. Start on the worst pivot-shaped sheet you own: convert the range to a Table, rewrite the messiest existing lookup to use MyTable[Column] and MyTable[#Headers], and see what breaks. Fix that one thing before touching anything else. The rest of the sheet is easier from there.