Excel Function

XMATCH

Enhanced MATCH with additional matching/search options.

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

Arguments

lookup_value

Required

Enter the value you want to look up.

lookup_array

Required

Specify the row/column range where the lookup key is searched.

match_mode

Optional

XMATCH/XLOOKUP match mode: 0 = exact, -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match.

search_mode

Optional

XMATCH/XLOOKUP search mode: 1 = first-to-last, -1 = last-to-first, 2 = binary ascending, -2 = binary descending.

Practice Preview

Use XMATCH to return the result with these settings (Lookup value: B2 (cell B2 (Product Name in column B)), Lookup array: B2:B8 (Product Name in column B), Match mode: 0 (exact match), Search mode: 1 (first to last)). You can set ranges by dragging from the start cell to the end cell in the same argument field.

Example Table

#	AProduct ID	BProduct Name	CPrice	DStock
2	P001	Apple	120	80
3	P002	Banana	98	120
4	P003	Notebook	450	35
5	P004	Pen	300	60
6	P005	Grapes	150	45
7	P006	Ruler	250	20
8	P007	Peach	86	55

How to Use (Formula)

=XMATCH(B2, B2:B8, 0, 1)

Result

Practice This Function

Start Function Practice Back to Function List

XMATCH FAQ

How do I use XMATCH in Excel?

Use XMATCH when you want to return the position of a match with flexible match rules. Enter Lookup value, Lookup array, Match mode in that order, then add optional arguments when needed.

Where can I practice XMATCH?

Use the "Start Function Practice" button to jump straight into XMATCH drills.

What arguments does XMATCH use?

The main arguments are Lookup value, Lookup array, Match mode, Search mode. Start with the required ones, then add optional arguments if needed.