What XMATCH does
XMATCH tells you where a value appears in a row or column. Its result is a position within the searched range, starting at 1. You can pass that position to another function, such as INDEX or CHOOSECOLS, to retrieve the corresponding data.
An exact match is the default. You don't need to sort the range for the ordinary first-to-last exact search.
Practical examples
Find the revenue column
Create a Table named Orders with the headers SKU, Region, Units, Revenue, and Rep in that order. In a cell outside the Table, enter:
=XMATCH("Revenue",Orders[#Headers])FormulaThe expected result is 4, because Revenue is the fourth header. It doesn't matter which worksheet column the Table starts in: the count starts at the Table's first header.
The header-based column selection tutorial uses this position to select the right field for a report.
Choose the first or last repeated label
In a separate ordinary range, enter SKU, Region, Region, and Revenue in B4:E4. Use a plain range for this example because Excel Tables require unique column names.
=XMATCH("Region",B4:E4,0)FormulaThe expected result is 2, the position of the first Region in C4. To search from the other end, use:
=XMATCH("Region",B4:E4,0,-1)FormulaThe expected result is 3, identifying Region in D4. Reversing the search changes which match is found; positions are still counted from B4, the start of the range.
Common mistakes and notes
A position isn't the matched value
The first example returns 4, not the text Revenue or a worksheet address. Use XLOOKUP when you want to retrieve a corresponding value directly, or pass XMATCH's position to another function.
Match mode and search mode do different jobs
The third argument controls what counts as a match. The fourth controls how the range is searched. To find the last exact match, use 0,-1; putting -1 in the third argument instead asks for an exact match or the next smaller value.
Binary searches require the correct sorting
Search mode 2 requires ascending data; -2 requires descending data. Microsoft warns that unsorted input can produce invalid results in these modes. Use the default search unless you have deliberately prepared the range for a binary search.
Wildcards need match mode 2
In wildcard mode, * matches a sequence of characters and ? matches one character. Use ~ to escape a wildcard character you want to match literally. The default exact-match mode doesn't enable wildcard matching. An exact search with no matching value returns #N/A; check the requested text and source entries before changing the match rule.