XMATCH in Excel: find a position the modern way

Syntax and arguments

lookup_value is what you search for and lookup_array a single row or column. The two optional arguments control how XMATCH searches:

match_mode: 0 exact match (the default), -1 exact match or the next smaller value, 1 exact match or the next larger value, 2 wildcard match with *, ? and ~.

search_mode: 1 first to last (the default), -1 last to first, 2 binary search on data sorted ascending, -2 binary search on data sorted descending.

The result is a number, the position inside lookup_array, counted from 1. On its own that is useful for "which row?" questions; combined with INDEX it becomes a full lookup. If nothing qualifies, XMATCH returns #N/A, which IFNA can turn into a message such as "Not found".

Step by step: putting weights into bands

The rate card lists the lower limit of each band: 0, 2, 5, 10 and 20 kg. A parcel belongs to the band with the largest lower limit that does not exceed its weight, which is exactly "exact match or next smaller". For P-02 at 7.8 kg, =XMATCH(C10,$B$2:$B$6,-1) skips 10 and 20 (too large), takes 5 as the next smaller value and returns position 3. =INDEX($C$2:$C$6,D10) then returns the rate in the 3rd row, 14.20.

Weights that sit exactly on a limit are exact matches: P-06 at 5 kg is band C, P-03 at 20 kg is band E. A weight below the first limit would return #N/A, which is why the first band starts at 0. Adding up all six rates gives 6.50 + 14.20 + 34.50 + 9.80 + 21.00 + 14.20 = 100.20.

XMATCH vs MATCH

MATCH does the same job but with older defaults. MATCH(value, range) without a third argument runs an approximate match that expects ascending data and can return a wrong position on unsorted data; XMATCH without a third argument runs an exact match. MATCH's -1 means "smallest value that is greater than or equal" on data sorted descending, while XMATCH's -1 means "exact or next smaller" and does not require any sort order. Copying the third argument from a MATCH formula into an XMATCH therefore changes the meaning.

Wildcards also differ: MATCH with 0 treats * and ? as wildcards automatically, XMATCH only with match_mode 2. That makes XMATCH safer for data that really contains asterisks, such as product names like "Pro*". The trade-off is compatibility: MATCH works in every Excel version, XMATCH only in Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. In a shared file that someone opens in Excel 2016, XMATCH shows #NAME?.

Worked example: shipping bands and a parcel log with XMATCH

A rate card by weight band (yellow, top) and six parcels. Column D finds each parcel's band with XMATCH, column E returns its rate with INDEX.

Worked example: shipping bands and a parcel log with XMATCH

	A	B	C	D	E
1	Band	From kg	Rate (USD)
2	A	0.0	6.50
3	B	2.0	9.80
4	C	5.0	14.20
5	D	10.0	21.00
6	E	20.0	34.50
7
8	Parcel	Customer	Weight (kg)	Band position	Rate
9	P-01	Alder & Co	1.4	1	6.50
10	P-02	Birchway	7.8	3	14.20
11	P-03	Alder & Co	20.0	5	34.50
12	P-04	Cedar Labs	3.2	2	9.80
13	P-05	Birchway	12.5	4	21.00
14	P-06	Alder & Co	5.0	3	14.20
15
16	Other XMATCH lookups				Result
17	Position of "Cedar Labs" in the log (exact match)				4
18	Position of the first customer starting with "Bir" (wildcard)				2
19	Last parcel for Alder & Co (search last to first)				P-06
20	Lightest parcel of at least 10 kg (exact or next larger)				P-05
21	Heaviest parcel (XMATCH on MAX)				P-03
22	Unknown customer, with IFNA				Not found
23	Total shipping cost				100.20

Fictitious rate card and customers. XMATCH needs Microsoft 365, Excel 2021 or later, Excel for the web or Google Sheets.

Search from the end: the last occurrence

Logs are usually sorted by time, so the most recent entry for a customer is the last one. With search_mode -1 XMATCH searches from the bottom: =INDEX(A9:A14,XMATCH("Alder & Co",B9:B14,0,-1)) returns P-06, Alder & Co's latest parcel, while a normal search would stop at P-01. With MATCH this needs an array trick or a helper column.

Next larger works on unsorted data as well: XMATCH(10,C9:C14,1) looks for 10 kg or the smallest weight above it and finds 12.5 kg, so the lightest parcel of at least 10 kg is P-05. Note that this is not "the first parcel heavier than 10 kg" in the order of the log; it is the closest value.

INDEX/XMATCH, XLOOKUP or MATCH?

For a simple "find this, return that", XLOOKUP is shorter than INDEX/XMATCH and accepts the same match and search modes. XMATCH is the better tool when you need the position itself: to feed several INDEX formulas from one search, to pick a row and a column in a two-way lookup, =INDEX(table,XMATCH(…),XMATCH(…)), or to compare positions, for example whether one item appears before another. Keep MATCH, as in INDEX MATCH, when the workbook must open in older Excel versions.

Common mistakes

• Using MATCH's match types in XMATCH. In XMATCH, -1 means "exact or next smaller" and 1 "exact or next larger". They do not mean the same as MATCH's -1 and 1.
• Expecting wildcards in exact mode. XMATCH("Bir*",B9:B14) looks for the literal text Bir*. Add match_mode 2.
• Binary search on unsorted data. search_mode 2 or -2 is fast but needs sorted data; on unsorted data it can return a wrong position without an error.
• No bottom band. With match_mode -1, a value below the smallest entry returns #N/A. Start a band table at 0 or catch the case with IFNA.
• INDEX range and XMATCH range out of line. The position counts from the first cell of lookup_array, so the INDEX range must start in the same row.