XMATCH function in Excel

XMATCH is to MATCH what XLOOKUP is to VLOOKUP, the modern upgrade. XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) returns a position, and it has the same clean design as XLOOKUP. Exact match is the default, -1/1 give approximate, 2 gives wildcard, and search_mode -1 searches in reverse. It's at its best when you want to separate the finding from the fetching, say for a dynamic column, or when you want a position and not a value.

How does a two-dimensional lookup work?

The headline job for both MATCH/XMATCH and INDEX is the two-dimensional lookup: fetching a value from a matrix where you must match both a row label and a column label. Picture a price matrix with products down the side and regions across the top:

      A          B       C       D         E
1     Product    East    West    Central   South
2     Widget     12.50   13.00   12.75     13.25
3     Gadget     8.20    8.50    8.35      8.60
4     Gizmo      21.00   21.75   21.25     22.00
5     Sprocket   5.40    5.60    5.50      5.75

To fetch the price for a product in H2 and a region in H3, the standard idiom is INDEX steered by two MATCHes. One picks the row and the other picks the column:

=INDEX(B2:E5, MATCH(H2, A2:A5, 0), MATCH(H3, B1:E1, 0))

The first MATCH finds the product row and the second finds the region column. INDEX reads the cell where they cross. This is the workhorse 2-D lookup. Later on, a two-variable Data Table automates this same row/column intersection for DCF sensitivities.

The XLOOKUP-inside-XLOOKUP version is worth knowing, because it shows you how XLOOKUP returns arrays. The inner XLOOKUP matches the region and returns the entire column of prices for that region. The outer XLOOKUP then matches the product against that returned column:

=XLOOKUP(H2, A2:A5, XLOOKUP(H3, B1:E1, B2:E5))

The inner XLOOKUP(H3, B1:E1, B2:E5) returns a whole 4-row column. Its return_array is the full 4×4 matrix, so matching one column of the header hands back that column of the body. The outer one then picks the product's row from it. Both formulas return the same price, so use whichever reads more clearly to you. XMATCH can drive the INDEX version too, as in =INDEX(B2:E5, XMATCH(H2,A2:A5), XMATCH(H3,B1:E1)). Exact match is its default, which saves you typing the ,0.