Skip to content

Excel function · Lookup & reference

Excel XMATCH function

XMATCH returns the position of a value in a column or row: the row of a customer's latest order, the smallest box a product fits in. It is available in Microsoft 365 and Excel 2021.

In Turkish Excel: ÇAPRAZEŞLEŞTİR

What it does

XMATCH does not return the value itself but its position: if the value is in the third cell of the range, the result is 3. On its own that answers "is it in the list, and where?"; combined with INDEX it lets you pull a value from another column.

It is the modern version of MATCH and differs in three ways: exact match is the default, approximate match does not need a sorted list, and the fourth argument can start the search from the end. Use match_mode 2 for wildcards (*, ?).

If you want the value rather than its position, XLOOKUP is shorter. For files that will be opened in Excel 2019 or earlier, use MATCH.

Syntax

English Excel=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Turkish Excel=ÇAPRAZEŞLEŞTİR(aranan; arama_dizisi; [eşleştirme_modu]; [arama_modu])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
lookup_valueRequiredThe value whose position you want, or the cell that holds it. The search is not case-sensitive.
lookup_arrayRequiredThe single column or row to search, e.g. B2:B7.
match_modeOptional0 or omitted: exact match. -1: exact match or the next smaller value. 1: exact match or the next larger value. 2: wildcard match.
search_modeOptional1 or omitted: search from first to last. -1: search from last to first and return the position of the last match. 2 and -2: binary search on a sorted list.

Examples

1Find a customer's latest order

Orders are listed by date. In F2 the search starts from the end (search_mode -1) and returns the position of the last row for the customer in E2; in G2, INDEX takes the date at that position.

F2fx=XMATCH(E2,B2:B7,0,-1)
Find a customer's latest order: example table
ABCDEFG
1DateCustomerAmountCustomerLast Order PositionLast Order Date
22-Sep-2026North Market3,200North Market514-Oct-2026
39-Sep-2026Atlas Ltd1,850
417-Sep-2026North Market4,100
53-Oct-2026Blue Electric2,600
614-Oct-2026North Market3,750
728-Oct-2026Atlas Ltd2,900
  • F2=XMATCH(E2,B2:B7,0,-1)
  • G2=INDEX(A2:A7,F2)

North Market appears three times; because the search starts from the end, the position is 5. INDEX returns the date at that position: 14-Oct-2026.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Pick the smallest box a product fits in

Boxes are listed by volume in column G. With match_mode 1, the next larger value is taken when there is no exact match, which gives the smallest box that fits. In column D, INDEX turns the position into a box name.

C2fx=XMATCH(B2,$G$2:$G$5,1)
Pick the smallest box a product fits in: example table
ABCDEFG
1OrderVolume (L)Box PositionBoxBoxVolume (L)
2SO-501142MediumSmall10
3SO-502383LargeMedium25
4SO-503252MediumLarge40
5SO-50481SmallJumbo60
  • C2 (filled down to C5)=XMATCH(B2,$G$2:$G$5,1)
  • D2 (filled down to D5)=INDEX($F$2:$F$5,C2)

SO-501 needs 14 litres and does not fit the 10-litre box; mode 1 finds the next larger size, 25 litres, so D2 shows Medium. SO-503, at exactly 25 litres, gets the same box: Medium.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The value is not in the list, or approximate match found no suitable smaller or larger value (e.g. the value is larger than everything in the list with mode 1).

Fix: Check the spelling and spaces; wrap the formula in IFERROR to show a message.

#VALUE!

Cause: One of the modes has an invalid number (e.g. match_mode 3).

Fix: Use 0, -1, 1 or 2 for match_mode and 1, -1, 2 or -2 for search_mode.

#NAME?

Cause: The file was opened in a version without XMATCH.

Fix: Use MATCH: =MATCH(E2,A2:A6,0).

Good to know

  • The result is the position inside the range, not the row number on the sheet: in B5:B9 the first cell is 1.
  • To test whether a value is in a list, use =ISNUMBER(XMATCH(E2,A2:A6)); it returns TRUE or FALSE (ISNUMBER).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Show each customer's latest order date from the order list in a separate summary table

Convert to table

Bring your order list into the tool and type this. Nexell finds each customer's last record with real Excel formulas and shows it in the preview; in the downloaded file, the summary updates when a date in the list changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between XMATCH and MATCH?

MATCH uses approximate match by default and needs a sorted list; XMATCH uses exact match by default. XMATCH can also search from the end and does not need sorting for approximate match. MATCH works in every version; XMATCH needs Microsoft 365 or Excel 2021.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free