ExcelFlux

XMATCH function

the relative position of an item in an array or range of cells.

Lookup and reference · Excel 2021+

Where can you use it?

Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.

Scenario in the workbook

Get the position of a code. The default is exact matching.

A worked example from the workbook

RowAB
6InputComparison
7E01E01
8E02E02
9E03E03
Record 1
=XMATCH(A7,$B$7:$B$9)

Expected result1

See the inputs and instructions for all 3 examples

Syntax

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

Copyable formulas

English Excel · en-US
=XMATCH(A7,$B$7:$B$9)
Turkish Excel · tr-TR
=ÇAPRAZEŞLEŞTİR(A7;$B$7:$B$9)

Worked examples

Get the position of a code. The default is exact matching.

Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.

Row A B
6 Input Comparison
7 E01 E01
8 E02 E02
9 E03 E03
Case Formula Expected result
Record 1 =XMATCH(A7,$B$7:$B$9) 1
Record 2 =XMATCH(A8,$B$7:$B$9) 2
Record 3 =XMATCH(A9,$B$7:$B$9) 3
Microsoft Excel reference