MATCH vs XMATCH in Excel

MATCH vs XMATCH is the same kind of upgrade as VLOOKUP to XLOOKUP. Both functions return the position of a value in a list, but XMATCH uses an exact match by default, can search from the bottom up, and handles approximate matches without sorted data. MATCH still matters because it works in every version of Excel and appears in countless INDEX MATCH formulas. This guide is for anyone who uses INDEX MATCH or is preparing for an Excel test.

MATCH vs XMATCH at a glance#

XMATCH keeps what MATCH does and fixes its risky default.

What MATCH and XMATCH actually return#

Both return a number: where the value sits in the list, counting from 1. They don't return the value itself. That is why you almost always see them inside INDEX, which takes a position and returns the item there.

Here is a list of sales reps:

The same lookups, side by side#

Task 1: find a position (exact match)#

=MATCH("Dana Kim",A2:A8,0)
=XMATCH("Dana Kim",A2:A8)

Both return 4: Dana Kim is the fourth name in A2:A8 (row 5 of the sheet). MATCH needs the 0 for an exact match. XMATCH does an exact match unless you say otherwise.

If you leave the 0 off MATCH, it does an approximate match. That expects the list to be sorted A to Z. On this unsorted list it can return a wrong position or #N/A, with nothing to warn you.

Task 2: return a value with INDEX#

With a name in F1:

=INDEX(C2:C8,MATCH(F1,A2:A8,0))
=INDEX(C2:C8,XMATCH(F1,A2:A8))

For Dana Kim, both return 72,300. The position from MATCH or XMATCH tells INDEX which row of C2:C8 to bring back. Our guide to INDEX MATCH in Excel explains the pattern step by step, including left lookups.

Task 3: find the last match#

Which row holds the last East rep?

=XMATCH("East",B2:B8,0,-1)

Search mode -1 searches from the bottom up, so XMATCH returns 7 (Gus Patel). MATCH always searches from the top, so =MATCH("East",B2:B8,0) returns 1 (Ana Lopez), and it has no option to start from the bottom. This is handy for "most recent order" questions when the newest rows are at the bottom.

Task 4: find a tier (approximate match)#

A commission table lists the minimum sales for each tier, sorted from smallest to largest: 0, 10,000, 25,000 and 50,000, in A2:A5. For a sale of 32,000:

=MATCH(32000,A2:A5,1)
=XMATCH(32000,A2:A5,-1)

Both return 3, the 25,000 tier. Notice the codes: MATCH uses 1 for "exact or next smaller", but XMATCH uses -1 for the same thing. XMATCH also doesn't need the tiers sorted for this, while MATCH does.

When to use MATCH#

Use MATCH when compatibility matters. It is the better choice when:

• The file must open in Excel 2019 or older. XMATCH shows #NAME? there.
• You are editing a workbook full of INDEX MATCH formulas, and want to keep one style.
• A job test asks for INDEX MATCH by name. It is a classic interview topic; see our list of Excel interview questions.
• You want wildcards with the simplest syntax: =MATCH("Dana*",A2:A8,0) works directly.

Always write the third argument. Make 0 a habit, and only use 1 or -1 on data you have sorted on purpose. The MATCH function guide covers each match type.

When to use XMATCH#

Use XMATCH for new work when everyone has Excel 2021, Excel 2024 or Microsoft 365. It is the better choice when:

• You want a safe default. Forgetting an argument can't silently switch you to approximate match.
• You need the last match, such as a customer's latest order: search mode -1.
• You need the next smaller or larger value on unsorted data, such as the closest tier or the nearest date.
• You want the position of a calculated value. =XMATCH(MAX(C2:C8),C2:C8) returns 4, the row of the top seller, ready to feed into INDEX.
• You are building a two-way lookup, with one XMATCH for the row and one for the column inside INDEX:

=INDEX(A2:C8,XMATCH("Dana Kim",A2:A8),XMATCH("Sales",A1:C1))

The first XMATCH finds the row (4), the second finds the column (3), and INDEX returns 72,300. Change "Sales" to "Region" and the same formula returns South.

Common mistakes with MATCH and XMATCH#

These cause most wrong positions:

1. Leaving out MATCH's third argument. The default is approximate match. Add 0.
2. Mixing up the approximate codes. 1 means "next smaller" in MATCH but "next larger" in XMATCH. Check which function you are in before copying a formula.
3. Expecting XMATCH wildcards by default. =XMATCH("Dana*",A2:A8) looks for the literal text "Dana*" and returns #N/A. Use =XMATCH("Dana*",A2:A8,2).
4. A lookup range that doesn't line up with the INDEX range. If MATCH searches A2:A8 but INDEX returns from C3:C9, every result is one row off. Start both ranges on the same row.
5. Searching more than one column. Both functions need a single row or a single column. =MATCH(F1,A2:C8,0) returns #N/A.
6. Confusing the position with the row number. Dana Kim is at position 4 in A2:A8, but she is on row 5 of the sheet, because the range starts at row 2. Feed the position into INDEX on a range that starts on the same row, rather than using it as a row number.
7. Extra spaces or numbers stored as text. "Dana Kim " and "Dana Kim" don't match, and neither do the number 101 and the text "101". Clean the data first.

Do job tests use MATCH or XMATCH?#

Many Excel assessments still test INDEX MATCH, because it works in every version. Some newer tests run on Microsoft 365 and accept XMATCH or XLOOKUP. If the task doesn't name a function and you don't know the version, INDEX(…, MATCH(…, 0)) is the safest answer. If you take the test in Google Sheets or on a Mac, the formulas are the same, but some shortcuts and features differ; see Excel vs Google Sheets for job tests and Excel on Mac vs Windows.

Knowing the syntax is only half of it. In a test you have to type the formula yourself, under time pressure, which is why we argue for hands-on practice over video courses.

Our recommendation#

Learn MATCH first, with 0 every time, because INDEX MATCH is still everywhere and works in any version. Then switch to XMATCH for new files on modern Excel: its exact-match default removes the most common lookup bug, and search mode -1 solves "last match" problems MATCH can't.

In short: MATCH with 0 for compatibility and tests that name it, XMATCH for new files on Excel 2021, Excel 2024 or Microsoft 365.

Practise it#

Solve the INDEX and XMATCH exercise on this page. Then work through the INDEX and MATCH lesson, from a single position to a two-way lookup, and the modern Excel functions lesson, where you use XMATCH alongside SORTBY, TEXTSPLIT and spill references.