XLOOKUP vs VLOOKUP vs INDEX MATCH: Which Should You Use

Every Excel user eventually faces the same fork in the road: stick with VLOOKUP, learn INDEX MATCH, or jump to XLOOKUP. The honest answer is that each one still has a legitimate place in 2026, and the right choice depends on your Excel version, who else opens your files, and what the lookup actually needs to do. This guide lays out the syntax, the trade-offs, and a decision table so you can stop second-guessing.

The Three Contenders at a Glance

	VLOOKUP	INDEX MATCH	XLOOKUP
Introduced	Excel 1.0 era (decades ago)	Excel 1.0 era	Excel 365 / Excel 2021
Lookup direction	Right only	Any direction	Any direction
Default match	Approximate (dangerous)	Exact (with 0)	Exact (safe)
Column insert breaks it?	Yes	No	No
Built-in “not found” message	No	No	Yes
Works in Excel 2019 and earlier	Yes	Yes	No

That last row is the one most comparison articles bury. If your workbook gets opened in Excel 2016 or 2019, XLOOKUP shows up as a #NAME? error, and no amount of elegance saves you.

VLOOKUP: The One Everyone Knows

The syntax for the classic VLOOKUP function:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Concrete example. You have employee IDs in column A and salaries in column D of a table spanning A2:D500:

=VLOOKUP("EMP-1042", A2:D500, 4, FALSE)

This finds EMP-1042 in column A and returns the value from the 4th column. Two things bite people constantly:

1. The fourth argument defaults to TRUE (approximate match). Forget the FALSE and VLOOKUP can silently return the wrong row without an error. This is the single most common source of bad lookup data I’ve seen in real workbooks.
2. The column index is a hard-coded number. Insert a column between A and D and your 4 now points at the wrong field. The formula doesn’t error — it just returns the wrong thing. There are workarounds, but they add complexity.

VLOOKUP also can’t look left. If your IDs are in column D and the names you want are in column A, you’re out of luck without restructuring the data or switching tools. (Its sibling HLOOKUP has the same limitations, rotated 90 degrees.)

When VLOOKUP is still the right call: quick one-off lookups in a sheet only you will touch, or files shared with people who only know VLOOKUP and need to audit your work. It’s also fine — genuinely fine — for comparing two sheets when the data layout is simple and stable.

INDEX MATCH: The Power User’s Workhorse

INDEX MATCH is two functions combined: MATCH finds the row position, and INDEX retrieves the value at that position.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Same example as before, but now it survives column insertions and works in any direction:

=INDEX(D2:D500, MATCH("EMP-1042", A2:A500, 0))

The 0 in MATCH means exact match — always include it. The structural advantages over VLOOKUP:

• Looks left, right, up, down. Lookup column and return column are independent ranges.
• Survives layout changes. You reference the return column directly, not by counting.
• Lighter recalculation footprint. You reference two single columns instead of an entire table block, so Excel tracks fewer cells. On a 500,000-row workbook with hundreds of lookups, this is measurable.
• Two-way lookups. Add a second MATCH for the column and you get a row-and-column lookup VLOOKUP simply can’t do:

=INDEX(B2:F500, MATCH("EMP-1042", A2:A500, 0), MATCH("Salary", B1:F1, 0))

The downside is readability. A nested function with three ranges is harder for a colleague to parse at a glance, and harder to teach. If your team lives in Excel casually rather than professionally, expect questions.

When INDEX MATCH is the right call: you need backward compatibility with Excel 2019 or earlier and you need left lookups, robustness to column changes, or two-way lookups. It is the most portable powerful option — it works in every Excel version ever shipped, including old Mac versions and most third-party spreadsheet tools.

XLOOKUP: The Modern Default

XLOOKUP was built to fix everything wrong with VLOOKUP:

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

Our running example:

=XLOOKUP("EMP-1042", A2:A500, D2:D500, "Not found")

What you get over both older options:

• Exact match by default. No more silent approximate-match disasters.
• Built-in not-found handling. The fourth argument replaces the IFERROR(VLOOKUP(...), "Not found") wrapper most of us have typed a thousand times.
• Search from the bottom. Set search_mode to -1 to find the last matching row — getting a customer’s most recent order used to require ugly workarounds.
• Return multiple columns at once. Point return_array at B2:D500 and XLOOKUP spills all three values into adjacent cells. If you’ve never worked with spilled results, read up on how spill works in Excel first.
• Wildcard and binary search modes when you need them, via match_mode 2 and search_mode 2/-2 respectively.

There’s also XMATCH, the modernized MATCH, if you still want the INDEX-style composability with the newer defaults.

The one real limitation: version support. XLOOKUP requires Microsoft 365, Excel 2021 or later, or Excel for the web. Anyone on Excel 2019, 2016, or older perpetual licenses gets #NAME?. If you’re not sure what you or your collaborators are running, check your Excel version before committing a shared workbook to XLOOKUP.

Performance: What Actually Matters

For workbooks under ~50,000 rows, all three are effectively instant and performance should not drive your decision. Beyond that, a few practical observations from working with large models:

• Range size matters more than function choice. VLOOKUP(x, A:Z, 26, FALSE) referencing 26 full columns recalculates far more than any of these functions referencing two trimmed columns. Tight ranges beat clever functions.
• INDEX MATCH and XLOOKUP reference only the columns they need, which keeps dependency chains smaller than whole-table VLOOKUPs.
• Sorted data unlocks binary search. XLOOKUP with search_mode 2 (or MATCH with match type 1 on sorted data) searches in O(log n) instead of scanning every row — on hundreds of thousands of rows, this is the difference between a sluggish workbook and a snappy one. Sort your data first and this trick works in any of the three approaches.
• Avoid volatile helpers. It’s the OFFSET and INDIRECT wrapped around lookups that kill performance, not the lookups themselves.

Migration Advice: Moving Off VLOOKUP

If you’re modernizing existing workbooks, don’t do a big-bang rewrite. A pragmatic path:

1. Confirm everyone is on Excel 2021+/365 before introducing XLOOKUP anywhere. One Excel 2019 user on the distribution list vetoes it.
2. Convert formulas as you touch them, not all at once. A VLOOKUP that works and never changes is not a problem.
3. The mechanical translation is straightforward. =VLOOKUP(x, A2:D500, 4, FALSE) becomes =XLOOKUP(x, A2:A500, D2:D500) — lookup column, then return column, exact match assumed.
4. Convert ranges to Excel Tables first. Creating a table gives you structured references like Employees[Salary] that auto-expand as data grows — this benefits every lookup style and makes the converted formulas self-documenting.
5. Keep INDEX MATCH where compatibility is unknown. Files sent to external clients, templates downloaded by the public, anything destined for unknown environments — INDEX MATCH runs everywhere.

The Decision Table

Your situation	Use
Everyone is on Microsoft 365 / Excel 2021+	XLOOKUP — make it your default
File goes to unknown or older Excel versions	INDEX MATCH
Quick lookup in a personal scratch sheet	Whatever you type fastest (usually VLOOKUP)
Need to look left of the lookup column	XLOOKUP, or INDEX MATCH on old versions
Two-way lookup (row and column)	INDEX MATCH MATCH or XLOOKUP nested in XLOOKUP
Need last match instead of first	XLOOKUP with search_mode -1
Huge sorted dataset, speed critical	XLOOKUP binary search (search_mode 2)
Teammates only know VLOOKUP and must audit it	VLOOKUP — readability by your audience counts

My personal default since XLOOKUP became widely available: XLOOKUP for everything new, INDEX MATCH for anything that leaves my machine for parts unknown, and VLOOKUP only when I’m in someone else’s workbook matching their existing style.

FAQ

Is VLOOKUP being deprecated?

No. Microsoft has given no indication VLOOKUP will ever be removed — too many billions of existing formulas depend on it. It will keep working indefinitely; it just stopped being the best tool for new work.

Is XLOOKUP slower than INDEX MATCH?

In practice they’re comparable, and both outperform whole-table VLOOKUPs because they reference only the columns they need. On very large sorted datasets, XLOOKUP’s binary search mode (search_mode 2) is the fastest option of all. For typical workbooks, pick based on features and compatibility, not speed.

What happens if I open an XLOOKUP file in Excel 2016?

Cells that depend on XLOOKUP show a #NAME? error, because Excel 2016 doesn’t recognize the function. The last calculated values may display until something triggers recalculation, which makes the failure sneaky — the file can look fine at first glance. If older versions are in play, use INDEX MATCH instead.

Can XLOOKUP return multiple columns like a single formula?

Yes. Set the return array to multiple columns — for example =XLOOKUP("EMP-1042", A2:A500, B2:D500) — and the result spills across three adjacent cells. Make sure the cells to the right are empty, or you’ll get a #SPILL! error.