Part of the free Module 5: Excel Formulas and Functions · Function 103 of 105 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365 and Excel 2021 or later.
The Excel XMATCH function returns the position of a value in a range or array. It is the modern replacement for MATCH: exact match is the default, it can search from the bottom, it supports wildcards and it accepts the same match_mode and search_mode codes as XLOOKUP. Use it whenever you need a row or column number rather than the value itself.
XMATCH syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | Required | What it does | Notes and allowed values |
|---|---|---|---|
| lookup_value | Required | The value whose position you want. | Text, number, date, logical or cell reference. An array of values returns an array of positions. |
| lookup_array | Required | The single row or column to search. | Must be one row or one column; two-dimensional ranges return #VALUE!. |
| match_mode | Optional | How the match is made. | 0 = exact match (default) · -1 = exact or next smaller · 1 = exact or next larger · 2 = wildcard match with ?, * and ~ |
| search_mode | Optional | Search direction. | 1 = first to last (default) · -1 = last to first · 2 = binary search on ascending data · -2 = binary search on descending data |
How XMATCH works
XMATCH scans lookup_array in the order set by search_mode and returns the relative position of the first cell that satisfies match_mode. Position counts from 1 at the start of the range, not from the worksheet row number, so a match in cell A7 of the range A3:A20 returns 5. Text comparison is not case sensitive. When lookup_value is an array or spilled range, XMATCH returns one position per value as a dynamic array.
Unlike MATCH, the approximate modes (-1 and 1) do not require sorted data; XMATCH examines every value and returns the closest one on the requested side. Binary search modes 2 and -2 do require sorted data and exist only for speed on very large lists.
Version notes. XMATCH is available in Excel 365, Excel 2021 and later, and Excel for the web. Excel 2019 and 2016 show #NAME?; use MATCH there. Google Sheets added XMATCH in 2022 with identical arguments.
Worked examples
Example 1: position of an item in a list
A simple list of regions in A2:A5. You want to know where West sits.
| Row | A: Region | Formula | Result |
|---|---|---|---|
| 2 | North | =XMATCH("West", A2:A5) |
3 |
| 3 | East | ||
| 4 | West | ||
| 5 | South |
West is the third item in the range, so XMATCH returns 3. Note the answer is 3, not 4, because positions are relative to A2:A5.
Example 2: INDEX and XMATCH for a two-way lookup
A price grid lists products down column A and sizes across row 1. To find the price of a Medium Jacket, XMATCH supplies the row and the column numbers to INDEX.
| Product | Small | Medium | Large |
|---|---|---|---|
| Jacket | 45 | 49 | 54 |
| Shirt | 18 | 20 | 22 |
| Trousers | 30 | 32 | 35 |
=INDEX(B2:D4, XMATCH("Jacket", A2:A4), XMATCH("Medium", B1:D1))The first XMATCH returns 1 (row), the second returns 2 (column), and INDEX returns 49. This pattern works on any version that has XMATCH and is easier to read than MATCH with its trailing 0.
Example 3: last occurrence with search_mode -1
A log records the same status several times. To find the position of the most recent Late entry, search from the bottom.
| Row | A: Status | Formula | Result |
|---|---|---|---|
| 2 | On time | =XMATCH("Late", A2:A6, 0, -1) |
4 |
| 3 | Late | ||
| 4 | On time | ||
| 5 | Late | ||
| 6 | On time |
The last Late is the fourth item, so the result is 4. The default search returns 2. Wrap this in INDEX to pull the date or amount from the same row.
Example 4: next larger value for a shipping band
Parcel weights in A2:A5 are 1, 2, 5 and 10 kg. A parcel weighs 3.4 kg and you need the band that covers it.
=XMATCH(3.4, A2:A5, 1)match_mode 1 returns the position of the exact value or the next larger one, which is 5 kg in position 3. Use -1 instead when you need the band at or below the value, for example tax thresholds.
Example 5: count items above a threshold on sorted data
On a list sorted in descending order, XMATCH with match_mode 1 and search_mode -2 tells you how many values are greater than or equal to a target. With scores 95, 88, 76, 64, 51 in A2:A6:
=XMATCH(70, A2:A6, 1, -2)The result is 3, because the third item (76) is the smallest value at or above 70, so three scores qualify. This is a fast alternative to COUNTIF on very large sorted lists.
Tips and common mistakes
- XMATCH returns a position, not a value. Pair it with INDEX, OFFSET or CHOOSECOLS to fetch the data.
- Exact match is the default. Do not add 0 out of habit; it is harmless but unnecessary.
- Keep the range to one row or column. A two-dimensional lookup_array causes #VALUE!.
- Use XLOOKUP when you only need the value. XMATCH earns its place when you need the position for INDEX, or to test whether an item exists.
- Test for existence with ISNUMBER.
=ISNUMBER(XMATCH(D2, A2:A100))returns TRUE if the item is in the list. - Wildcards need match_mode 2. Without it, an asterisk is a literal character.
- Do not use binary search on unsorted lists. Modes 2 and -2 return wrong positions silently when the data is not sorted.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| #N/A | No match found. | Check spelling, spaces and text-versus-number formats; wrap in IFNA to show a message. |
| #VALUE! | lookup_array has more than one row and one column, or an invalid match_mode or search_mode code. | Use a single row or column; use only 0, -1, 1, 2 and 1, -1, 2, -2. |
| #NAME? | File opened in Excel 2019 or earlier, or misspelt name. | Use MATCH for older versions. |
| #SPILL! | lookup_value is an array and the cells needed for the result are not empty. | Clear the cells below or use a single lookup value. |
Practice exercise
- Type ten product names in A2:A11 and use XMATCH to return the position of the seventh one. Expected: 7.
- Rebuild Example 2 for the price of Large Trousers. Expected: 35.
- Add a duplicate product to the list and return the position of its last occurrence with search_mode -1.
- Write an ISNUMBER(XMATCH()) test in column B that shows TRUE when each item in column A also appears in column D.
Key takeaways
- XMATCH returns the relative position of a value, counting from 1.
- Exact match is the default, and approximate modes do not need sorted data.
- search_mode -1 finds the last occurrence.
- INDEX with XMATCH is the cleanest two-way lookup pattern.
- Available in Excel 365 and 2021 or later; MATCH remains the fallback for older files.
Related functions and lessons
- Excel Formulas and Functions course hub
- XLOOKUP: returns the value rather than the position, using the same mode codes.
- MATCH: the older equivalent for Excel 2019 and earlier.
- INDEX: turn a position into a value.
- OFFSET: build ranges from a position.
- Sort and Filter course: sorting data before using binary search modes.
- Microsoft Support: XMATCH function
Frequently asked questions
What is the difference between XMATCH and MATCH?
XMATCH defaults to an exact match, can search from the last item upwards, supports wildcards through match_mode 2 and does not need sorted data for approximate matches. MATCH defaults to approximate match, only searches top to bottom and needs sorted data for its 1 and -1 modes. XMATCH needs Excel 365 or 2021 or later.
Does XMATCH return the row number?
No. It returns the position within the range you searched. If the range starts at row 1 the two numbers are the same; otherwise add the starting row minus one. For example, a position of 5 in A3:A20 means row 7. Most formulas feed the position straight into INDEX, where the row number is not needed.
Can XMATCH find the last match?
Yes. Set search_mode to -1 and XMATCH searches from the bottom of the range to the top, returning the position of the last occurrence. Combine it with INDEX to return the most recent record for a customer, product or status without sorting the data.
Why does XMATCH return #N/A?
The lookup value was not found. Common causes are trailing spaces, numbers stored as text or the wrong column. Clean the data with TRIM or VALUE, or wrap the formula in IFNA to display a friendly message. With an approximate mode, #N/A means there was no value on the requested side.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built with these functions are available at NextGenTemplates.com.