XMATCH Generator
Find the position of a value in a range, with exact, approximate, wildcard, or reverse search.
XMATCH
Find the position of a value in a range, with exact, approximate, wildcard, or reverse search.
Cell or value to locate.
A single column or single row to search.
0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
1 first to last, -1 last to first, 2 binary ascending, -2 binary descending.
=XMATCH(A2, $D$2:$D$100, 0, 1)Worked Example
Product names are listed in D2:D100 and Widget B appears more than once. You want the position of the last occurrence.
=XMATCH("Widget B", $D$2:$D$100, 0, -1)Returns: Searching bottom-up, if the last Widget B sits in D20 the formula returns 19, its position within D2:D100 (D2 is position 1). Wrap it in INDEX to pull the matching value.
Checks Before You Paste
- •XMATCH returns a position number, not a value. Feed it to INDEX - =INDEX($E$2:$E$100, XMATCH(A2, $D$2:$D$100, 0, 1)) - or use two XMATCH calls inside one INDEX for a row-and-column lookup.
- •Search mode -1 walks the list backwards, which is the easy way to grab the LAST matching row (latest transaction, most recent status) without any array gymnastics.
- •Binary search modes 2 and -2 are fast but assume the data is genuinely sorted in that direction. On unsorted data they do not error - they return a wrong position or a stray #N/A, so only use them on data you control.
How XMATCH works
Availability: Excel 2021, Microsoft 365 and Excel for the web; also in Google Sheets. Excel 2019 and earlier do not have it and show _xlfn.XMATCH with a #NAME? error - use MATCH there.
XMATCH tells you where a value sits in a list. It takes a lookup value and a single column or single row, and returns the position within that range: the first cell is 1, the second is 2, and so on. It does not return the value itself, which is why it almost always appears inside INDEX, or beside another XMATCH to fix the row and the column of a two-way lookup.
It is the replacement for MATCH, and the differences are worth knowing before converting old formulas. XMATCH defaults to an exact match, whereas MATCH defaults to approximate - so a MATCH formula with its third argument omitted does not mean the same thing as an XMATCH formula with its third argument omitted. XMATCH can also search backwards, which is the clean way to find the last occurrence of a value rather than the first, and it offers a wildcard mode that MATCH applies automatically to text. Approximate matching in XMATCH does not require sorted data either, unless you explicitly choose one of the binary search modes.
match_mode takes four values: 0 for an exact match, -1 for exact or the next smaller item, 1 for exact or the next larger, and 2 to treat * and ? in the lookup value as wildcards. search_mode takes 1 to scan from first to last, -1 to scan from last to first, and 2 or -2 for a binary search that assumes the data is sorted ascending or descending. Binary search is faster on very large ranges, and on unsorted data it returns a wrong position or a stray #N/A without any warning, so keep it for ranges you control.
A value that is not found returns #N/A. Since XMATCH produces a number, that error propagates into whatever it feeds - INDEX, ROW arithmetic, or a chart series - so wrap the outer formula with IFNA rather than trying to catch it in the middle. Remember too that the position is relative to the range you searched: position 19 inside D2:D100 is sheet row 20, not row 19.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to locate: a cell reference, a number, text in quotes, or a logical value. With match_mode 2 it may contain the wildcards * and ?. |
lookup_array | Required | A single column or single row to search. A two-dimensional range returns #VALUE!. Whole-column references work but slow the formula down on large sheets. |
match_mode | Optional | 0 (default) exact match; -1 exact or next smaller; 1 exact or next larger; 2 wildcard match on text. Unlike MATCH, the default here is exact. |
search_mode | Optional | 1 (default) searches first to last; -1 searches last to first; 2 is a binary search on ascending data; -2 is a binary search on descending data. The binary modes give wrong answers silently if the data is not actually sorted that way. |
More worked examples
Employee IDs in B2:B200 and start dates in C2:C200. You want the start date for the ID typed in A2.
=INDEX($C$2:$C$200, XMATCH(A2, $B$2:$B$200))Returns: Returns the date from the row whose ID matches A2, or #N/A if the ID is not on the list.
No third argument is needed because XMATCH matches exactly by default, unlike MATCH, which would need a 0 here.
Quantity price breaks listed ascending in D2:D6 as 0, 100, 500, 1000 and 5000. An order of 750 units.
=XMATCH(750, $D$2:$D$6, -1)Returns: Returns 3. There is no exact match, so match_mode -1 takes the largest value not above 750, which is 500 in D4 - the third position in the range.
Feed that position to INDEX over the rates column to get the price. Unlike VLOOKUP's approximate mode, this does not require the list to be sorted, though a sorted list is easier to read.
Company names in B2:B50 where you want the first one ending in Ltd.
=XMATCH("*Ltd", $B$2:$B$50, 2)Returns: Returns the position of the first matching name - 7 if the first company ending in Ltd sits in B8.
Wildcards only work in match_mode 2. In mode 0 the asterisk is treated as a literal character and the match fails.
Common mistakes
- Confusing the position with the row number
- XMATCH counts from the top of lookup_array, not from row 1 of the sheet. Searching D2:D100 and getting 19 means the twentieth row of the worksheet. If you need a real row number, add the offset explicitly: XMATCH(...) + ROW($D$2) - 1.
- #VALUE! from a two-dimensional lookup_array
- The range must be one column or one row. Handing XMATCH a block such as $B$2:$D$100 returns #VALUE!. For a two-way lookup, use two XMATCH calls inside one INDEX - one over the header row, one over the key column.
- Binary search on data that is not sorted
- search_mode 2 and -2 jump around the range assuming an order that may not exist. On unsorted data they return a plausible but wrong position, or #N/A for a value that is clearly present. Only use them where the sort order is guaranteed, and default to 1 otherwise.
- Wildcard mode changing the meaning of your data
- With match_mode 2, a product code containing an asterisk or question mark stops being literal - searching for "ABC*" matches ABC123 rather than the code ABC*. Escape the character with a tilde ("ABC~*") or drop back to match_mode 0.
- #NAME? when the file is opened in older Excel
- Excel 2019 and earlier store the function as _xlfn.XMATCH and cannot evaluate it. If the workbook is shared with those versions, rewrite as MATCH with an explicit 0 for the match type, which is equivalent for exact matching.
Frequently Asked Questions
Four ways. XMATCH defaults to an exact match while MATCH defaults to approximate, so omitting the third argument means opposite things. XMATCH can search from the end of the list with search_mode -1. Its approximate modes do not require sorted data. And wildcards are opt-in through match_mode 2 rather than always active on text.
Because that is its job - it reports a position, and something else fetches the value. Wrap it in INDEX: =INDEX(return_range, XMATCH(lookup_value, lookup_range)). If all you want is the value, XLOOKUP does both steps in one call; XMATCH is for when you need the position itself, such as a two-way lookup or a row offset.
Set search_mode to -1, which walks the range from bottom to top and stops at the first match it meets - the last one in sheet order. That is the simplest way to get the most recent transaction or latest status without sorting the data or writing an array formula.
It returns the position of the largest value that is still less than or equal to your lookup value, which is what price breaks, tax bands and grade boundaries need. match_mode 1 does the mirror image, returning the smallest value greater than or equal to it. If nothing qualifies in either direction, the result is #N/A.
No - lookup_array has to be one-dimensional. For a row and column lookup, use one XMATCH over the header row and another over the key column, both inside a single INDEX: =INDEX($B$2:$M$50, XMATCH(A2, $A$2:$A$50), XMATCH(B1, $B$1:$M$1)).
Related Tools
INDEX MATCH Generator
The pairing XMATCH is built for, including the two-way row and column lookup.
XLOOKUP Generator
Returns the value directly when you do not actually need the position.
HLOOKUP Generator
The older way to search along a header row, for files that must open in Excel 2019.
VLOOKUP Generator
The legacy vertical lookup XMATCH formulas usually replace.