XLOOKUP vs INDEX MATCH — which one to use, and when

Updated August 14, 2026

Both of these do the same job: find a value in one column, return the matching value from another. The argument about which is better usually skips the question that actually decides it — which versions of Excel does your workbook have to open in?

Here is the short answer, then the detail.

• You control every machine, all on Microsoft 365 or Excel 2021 → use XLOOKUP. It is shorter, it handles not-found without a wrapper, and it reads in the order you think.
• The file goes to clients, or anyone on Excel 2019 or 2016 → use INDEX MATCH. XLOOKUP returns #NAME? there, and the formula is unrecoverable without editing it.
• Either way, stop writing new VLOOKUPs. That is the change that actually prevents bugs.

The two formulas, side by side

Finding an employee’s department from an ID:

=XLOOKUP(A2, Staff[ID], Staff[Department], "Not found")

=IFERROR(INDEX(Staff[Department], MATCH(A2, Staff[ID], 0)), "Not found")

XLOOKUP reads left to right in the order you would say it out loud: look for this, in here, return from there, and if you cannot find it say this.

INDEX MATCH reads inside out, which is why it takes longer to learn. MATCH finds which row the ID is in, and INDEX pulls that row number out of the department column. Once that clicks it stops being awkward, but there is no pretending it is the more intuitive of the two.

The MATCH default that returns wrong answers silently

If you take one thing from this page, take this.

=INDEX(Staff[Department], MATCH(A2, Staff[ID]))      ← the 0 is missing

MATCH has an optional third argument, match_type, and it defaults to 1, not 0. match_type 1 means “the largest value less than or equal to the lookup value”, and it assumes the lookup column is sorted ascending. On unsorted data it does not error. It returns whatever row it happened to land on.

So the formula above produces a department. Just not necessarily the right one. And because there is no error, nothing tells you.

Always write the 0:

=INDEX(Staff[Department], MATCH(A2, Staff[ID], 0))

XLOOKUP inverts this default — its match_mode is 0 (exact) unless you say otherwise — which is one of the genuinely better decisions in its design. It is also why migrating an old INDEX MATCH to XLOOKUP occasionally changes results: you may be replacing an accidental approximate match with a real exact one.

What XLOOKUP does that INDEX MATCH cannot do neatly

Built-in not-found handling. The fourth argument replaces wrapping the whole formula in IFERROR. That matters for more than brevity: IFERROR swallows every error, so a #REF! from a deleted column gets reported as “Not found” too. XLOOKUP’s if_not_found only catches an actual failed match, and lets real errors surface.

Search from the bottom. search_mode of -1 searches last-to-first, which gives you the most recent record in a log without sorting or a helper column:

=XLOOKUP(A2, Log[ID], Log[Status], "None", 0, -1)

Return more than one column. Because the return array can be several columns wide, one formula spills the whole record:

=XLOOKUP(A2, Staff[ID], Staff[[Department]:[Manager]])

Wildcards as an explicit choice. match_mode 2 enables * and ?. In INDEX MATCH, wildcards are always on for text, which occasionally bites you when a legitimate value contains an asterisk.

Full signature, for reference:

XLOOKUP(lookup_value, lookup_array, return_array,
        [if_not_found], [match_mode], [search_mode])

What INDEX MATCH still does better

It works everywhere. Excel 97 to Excel 365, on any machine, in any file format. For anything you send outside your own organisation this is not a small advantage — it is usually the deciding one.

Two-way lookups read better. Finding the value at the intersection of a row and a column, using one INDEX and two MATCHes:

=INDEX(Data, MATCH($A2, RowHeaders, 0), MATCH(B$1, ColHeaders, 0))

The XLOOKUP equivalent nests one XLOOKUP inside another and is harder to follow:

=XLOOKUP($A2, RowHeaders, XLOOKUP(B$1, ColHeaders, Data))

One MATCH, many INDEXes. If you are pulling eight columns for the same record, calculate the row position once in a helper cell and reference it eight times. Eight XLOOKUPs each repeat the search. This is the one case where the performance difference is real and worth caring about.

Head to head

Why VLOOKUP is the one to drop

Neither of these is really competing with VLOOKUP, because VLOOKUP has a structural flaw the other two do not:

=VLOOKUP(A2, Staff, 4, FALSE)

That 4 means “the fourth column of the range”. Insert a column anywhere in the first four and the formula still calculates, still returns a value, and now returns the wrong one. No error. Nothing to notice.

Both XLOOKUP and INDEX MATCH reference the return column directly, so inserting a column moves the reference with it. That single difference is worth more than every other point on this page.

VLOOKUP also carries the entire table range, so on a 40-column table it drags 40 columns through every calculation to return one.

Migrating from VLOOKUP

Mechanical, once you see the shape:

=VLOOKUP(A2, $D$2:$H$500, 3, FALSE)

=XLOOKUP(A2, $D$2:$D$500, $F$2:$F$500, "Not found")

=INDEX($F$2:$F$500, MATCH(A2, $D$2:$D$500, 0))

Count across from the first column of the old range to find which column the index number pointed at — here, 3 means column F. Then reference D and F directly.

Better still, convert the source to an Excel Table first with ctrl-T and use the column names. Staff[ID] and Staff[Department] cannot be broken by inserting a column, they grow automatically when rows are added, and anyone reading the formula can tell what it does without going to look at the sheet.

So which one

Use XLOOKUP when the workbook stays on Microsoft 365 or Excel 2021. Use INDEX MATCH when it does not, or when you are doing a two-way lookup, or when one row position feeds many columns.

And whichever you pick, write it against an Excel Table with the match type stated explicitly. Most lookup bugs we see in real workbooks are not about which function someone chose — they are a missing 0, a hard-coded column number, or a range that stopped covering the data three months ago.

Lookups are the first thing we teach in every intermediate Excel session, because they are where the most manual cross-referencing disappears. The Excel and Office 365 courses work through this on your own data, and the course schedule lists what is running next. If you would rather read on, the IFS function covers the conditional side of the same problem.