XMATCH in Excel
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) returns the position of a value in a row or column, counted from 1. Unlike MATCH it defaults to an exact match, and it can search from the bottom up or return the next smaller or next larger item.
Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Part | What it is |
|---|---|
lookup_value | The value to find. |
lookup_array | One row or one column to search. |
match_mode | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match with * and ?. |
search_mode | 1 first to last (default), -1 last to first, 2 and -2 binary search on sorted data. |
Examples
=XMATCH("Kiwi", A2:A20)Result: The position of Kiwi in A2:A20, for example 4
Exact match without typing a third argument.
=INDEX(C2:C20, XMATCH(E2, A2:A20))Result: The value in column C on the row where column A equals E2
The same job as INDEX and MATCH, one argument shorter.
=XMATCH(E2, A2:A20, 0, -1)Result: The position of the last row that equals E2
Search mode -1 starts at the bottom, which finds the most recent entry in a log.
=XMATCH(4500, B2:B6, -1)Result: The position of the largest value that is 4500 or less
Unlike MATCH, the range does not have to be sorted for match mode -1 or 1.
Common mistakes
- #N/A means no exact match was found. Check for trailing spaces and numbers stored as text before changing the match mode.
- #NAME? means the Excel version is older than Excel 2021. Use MATCH with 0 as the third argument there.
- Copying MATCH habits: in MATCH the third argument 1 means approximate match on sorted data, in XMATCH 1 means exact or next larger.
- Passing a two-dimensional range returns #VALUE!. XMATCH searches a single row or a single column.
Practice XMATCH
These drills check your answer on live data, free, no account needed.
- Finishing positionThe runners are listed in finishing order. Return the finishing position of the runner named in F1. Answer in F2.
- Look left with INDEX and MATCHThe employee name is in the last column, so VLOOKUP cannot reach the salary to its left. Use INDEX and MATCH to return the salary of the employee named in F1. Answer in F2.
- The most recent priceThe price log lists every price change, oldest first, so a product appears several times. Return the most recent price of the product in F1, in F2.
Questions
What is the difference between MATCH and XMATCH?
XMATCH defaults to exact match, can search from last to first, supports next smaller and next larger without sorting, and takes wildcards through match mode 2. MATCH defaults to approximate match and needs 0 for exact.
Does XMATCH work in Excel 2016 or 2019?
No. XMATCH is in Excel 2021 and Microsoft 365, and in Google Sheets. In older versions use MATCH(value, range, 0).
How do I find the last occurrence of a value?
=XMATCH(value, range, 0, -1) searches from the bottom and returns the position of the last match.
Related
- MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.
- INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
- INDEXHow the Excel INDEX function returns the cell at a given row and column of a range, with examples for a single column, a grid and the pairing with MATCH.