XMATCH function in Excel

XMATCH returns the position of a value in a range, with exact match as the default and a choice of search direction.

XMATCH is the newer form of MATCH, introduced together with XLOOKUP. It returns a position in the same way, and fixes the things that made MATCH risky. It is in Excel 2021 and Microsoft 365, and not in earlier versions.

Syntax

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

Argument	What it is
`lookup_value`	The value to look for.
`lookup_array`	A single row or column to search.
`match_mode`	0 exact match (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.
`search_mode`	1 first to last (the default), -1 last to first.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=XMATCH("Ravi",B2:B13)
Result: 2
The first row on which Ravi appears: the 2nd sale in the list.
=XMATCH("Ravi",B2:B13,0,-1)
Result: 10
Search mode -1 starts from the bottom: Ravi's most recent sale is the 10th in the list.
=INDEX(F2:F13,XMATCH("Ravi",B2:B13,0,-1))
Result: 1500
With INDEX: the amount of Ravi's latest sale.
=XMATCH(1000,F2:F13,1)
Result: 1
Match mode 1: there is no sale of exactly 1000, so the next larger amount is used, 1200, which sits in position 1.
=XMATCH("B*",D2:D13,2)
Result: 3
Wildcard mode: the first product that starts with B.

What is different from MATCH

Exact match is the default. With MATCH you must remember to type 0. With XMATCH you get an exact match unless you ask for something else.
It can search from the end. Search mode -1 returns the last match, not the first. In a list kept in date order, that is the latest entry, which is very often the one you want.
Approximate matches do not need a sorted list. MATCH demands a sorted range for its approximate modes. XMATCH with match mode -1 or 1 checks every item and finds the nearest one below or above.
The numbers mean something else. In MATCH, match type 1 means "next smaller". In XMATCH, match mode 1 means "next larger" and -1 means "next smaller". Take care when converting old formulas.

Match modes

match_mode	Meaning
0	exact match
-1	exact match, or the next smaller item
1	exact match, or the next larger item
2	wildcard match, with `*` and `?`

Typical uses

The latest entry for a person or product. Pair it with INDEX, as in the third example. Before XMATCH this needed an awkward array formula.

The nearest value in an unsorted list. =XMATCH(target, range, -1) finds the closest value at or below a target wherever it sits.

Position in a ranking. If a column of scores is sorted from highest to lowest, the XMATCH of a score is its place.

XMATCH or XLOOKUP?

If you want the value itself, XLOOKUP does it in one step. Use XMATCH when you need the position: to feed INDEX with a row and a column, to choose a column by its heading, or to test whether something is in a list with ISNUMBER(XMATCH(...)).

Common mistakes

#NAME? means the Excel version is too old for XMATCH. Use MATCH.
#N/A means nothing matched. Check for stray spaces and for numbers stored as text.
A two-dimensional range. As with MATCH, the lookup array must be one row or one column.

In Google Sheets

Google Sheets has XMATCH too, with the same arguments.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.