# XMATCH function in Excel

> XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.

Source: https://spreadsheetpractice.com/functions/xmatch · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.
- **Syntax:** `=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])`
- **Most common use:** `=XMATCH("Ito",B2:B5)` returns 2 (Exact match is the default, so no third argument is needed)
- **Available in:** Excel 2021, 2024 and Microsoft 365
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** The position of the match within lookup_array, or #N/A when there is no match.
- **Google Sheets:** Available in Google Sheets with the same arguments.

## What it does

XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data. Pair it with INDEX the same way you would pair MATCH.

## Syntax

`=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])`

| Argument | Required? | What it is |
|---|---|---|
| `lookup_value` | Required | The value to find. |
| `lookup_array` | Required | The single row or column to search. |
| `match_mode` | Optional | 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
| `search_mode` | Optional | 1 searches first to last (default), -1 last to first, 2 or -2 a binary search on sorted data. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=XMATCH("Ito",B2:B5)` | 2 | Exact match is the default, so no third argument is needed |
| E3 | `=XMATCH("Halvorsen",B2:B5,0,-1)` | 3 | Searching from the bottom finds Halvorsen's latest order, in position 3 |
| E4 | `=INDEX(C2:C5,XMATCH("Halvorsen",B2:B5,0,-1))` | $980 | With INDEX: the amount of that latest order |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Customer | Amount |  | Result |
| 2 | 3/2/2026 | Halvorsen | $420 |  | 2 |
| 3 | 3/5/2026 | Ito | $1,150 |  | 3 |
| 4 | 3/9/2026 | Halvorsen | $980 |  | $980 |
| 5 | 3/12/2026 | Mbeki | $260 |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #NAME? | XMATCH needs Excel 2021, Excel 2024 or Microsoft 365. In older versions use MATCH with 0 as the third argument. |
| Copying MATCH's match type | MATCH's 1 means "approximate, sorted ascending", but XMATCH's 1 means "exact or next larger". Converting old formulas, change MATCH(x,range,0) to XMATCH(x,range) and recheck any 1 or -1. |
| #N/A | No match, often because of a trailing space or a number stored as text. |

## Tips

- search_mode -1 returns the last occurrence, which is the latest entry in a log sorted oldest first.
- Use match_mode 2 for wildcards: =XMATCH("Hal*",B2:B5,2).

## Related functions

- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.

## Questions

### What is the difference between MATCH and XMATCH?

XMATCH defaults to an exact match, can search last to first, and finds next-larger or next-smaller values without sorted data. MATCH works in every version of Excel.

### How do I find the last match in a list?

Use XMATCH with search_mode -1: =XMATCH(E2,B2:B100,0,-1). Wrap it in INDEX to return a value from that row.

## Practice

A graded XMATCH exercise ("Latest price paid") is on [the XMATCH page](https://spreadsheetpractice.com/functions/xmatch). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
