XLOOKUP vs VLOOKUP: Which One Should You Use?

If your Excel has XLOOKUP, use XLOOKUP. If it doesn't, use VLOOKUP. That's the honest answer, and for most people the decision ends there — it depends on your Excel version, not on your skill level or the kind of data you have.

The complication is that plenty of people have XLOOKUP and shouldn't use it, because the files they build get opened by colleagues who don't. This guide covers both: what XLOOKUP actually does better, and the specific situations where reaching for it will break someone else's spreadsheet.

Do you have XLOOKUP?

Type =XLOOKUP( into any cell. If Excel autocompletes the function name, you have it. If you get #NAME?, you don't.

Version	XLOOKUP available?
Microsoft 365	Yes
Excel 2024	Yes
Excel 2021	Yes
Excel 2019 and earlier	No
Excel for the web	Yes
Google Sheets	Yes (added 2024)

Excel 2019 is still widely installed in companies that buy perpetual licences rather than subscriptions. That single row is the reason this article isn't just one sentence long.

The same lookup, written both ways

Find a product code in column A and return its price from column B:

=VLOOKUP(D2, $A$2:$B$100, 2, FALSE)

=XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100)

The XLOOKUP version is longer to read but simpler to think about. You name the thing you're searching for, the column to search in, and the column to return. There is no counting, and no fourth argument to remember.

Same result, two approaches — XLOOKUP names the return column instead of counting to it.

Five things XLOOKUP does better

1. It looks left

VLOOKUP can only return a column to the right of the one it searches. If your codes sit in column C and the names you want are in column A, VLOOKUP simply cannot do it — you have to rearrange the sheet or switch functions.

=XLOOKUP(D2, $C$2:$C$100, $A$2:$A$100)

XLOOKUP doesn't care about direction. The search range and return range are independent, so either can sit on either side.

2. Exact match is the default

Forget the FALSE in a VLOOKUP and it silently switches to approximate match, returning the closest lower value instead of an error. That's the failure mode people don't catch, because a wrong number looks like a right one.

XLOOKUP defaults to exact match. You have to deliberately ask for anything else.

3. No column index to break

A VLOOKUP's 2 means "the second column of the range." Insert a new column inside that range and the 2 now points somewhere else — but the formula still calculates, so nothing warns you.

XLOOKUP names the return range directly. Insert a column and the range adjusts itself, like any other Excel reference.

4. Handles "not found" without IFERROR

=XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100, "Not found")

The fourth argument is the fallback. VLOOKUP needs the whole formula wrapped in IFERROR to do the same thing — and IFERROR hides every error, including the ones you'd want to know about, like a broken reference.

5. Returns more than one column

Give XLOOKUP a return range several columns wide and it spills the whole matching row across your sheet:

=XLOOKUP(D2, $A$2:$A$100, $B$2:$E$100)

With VLOOKUP you'd write four separate formulas with four different index numbers.

Two features people miss

Search from the bottom up. The sixth argument controls direction. -1 searches from the last row backwards, which is how you find the most recent entry in a log:

=XLOOKUP(D2, $A$2:$A$1000, $B$2:$B$1000, "None", 0, -1)

Wildcard matching. Set the fifth argument to 2 and you can search with * and ?:

=XLOOKUP("PRD-1*", $A$2:$A$100, $B$2:$B$100, "None", 2)

When VLOOKUP is still the right choice

You share files with people on older Excel

This is the big one. Save an XLOOKUP file and open it in Excel 2019, and the formula becomes:

=_xlfn.XLOOKUP(D2,A2:A100,B2:B100)

It returns #NAME? and stays broken. Worse, the last calculated values are usually still cached in the file, so it can look fine until someone edits a cell and the whole column errors at once.

If you don't control what everyone in the chain is running, VLOOKUP is the safer default. This is also why VLOOKUP is still worth learning properly even in 2026.

The file is going into an old system

Accounting packages, ERP exports and reporting tools that read .xlsx files often use libraries that predate XLOOKUP. They read the formula as unknown and return nothing.

Someone else has to maintain it

Not a technical reason, but a real one. VLOOKUP is the function almost every office worker recognises. If your sheet gets handed over, a VLOOKUP is more likely to be understood than a six-argument XLOOKUP.

Converting a VLOOKUP to an XLOOKUP

In your VLOOKUP	In the XLOOKUP
Lookup value	Stays the first argument
First column of the range	Becomes the lookup array
The column the index points at	Becomes the return array
FALSE	Delete it — exact is the default
Surrounding IFERROR	Becomes the fourth argument

Worked through:

=IFERROR(VLOOKUP(D2, $A$2:$C$100, 3, FALSE), "None")

=XLOOKUP(D2, $A$2:$A$100, $C$2:$C$100, "None")

Is XLOOKUP slower?

On a normal sheet, no difference you'd notice. On very large workbooks — tens of thousands of lookup formulas — XLOOKUP can be slower than VLOOKUP because it evaluates two ranges instead of one. If a file recalculates sluggishly, that's worth testing, but it is not a reason to avoid XLOOKUP in ordinary work.

Common questions

Should I rewrite all my existing VLOOKUPs?

No. Working formulas aren't a problem to fix. Use XLOOKUP for new work, and convert an old one only when you're already editing it or it has actually broken.

Does XLOOKUP work in Google Sheets?

Yes, since 2024. Older shared Sheets files may still be built around VLOOKUP, and both continue to work side by side.

Can XLOOKUP replace HLOOKUP too?

Yes. It searches rows or columns depending on the shape of the ranges you give it, so there's no separate horizontal version to learn.

What about INDEX MATCH?

INDEX MATCH solves the same problems as XLOOKUP — left lookups, resilience to inserted columns — and works in every Excel version ever released. If you need XLOOKUP's flexibility but can't rely on people having it, INDEX MATCH is the answer.

The short version

• Have XLOOKUP and control who opens the file? Use XLOOKUP.
• Sharing with unknown or older Excel versions? Use VLOOKUP.
• Need to look left but can't use XLOOKUP? Use INDEX MATCH.
• Never leave an XLOOKUP in a file destined for Excel 2019 — it degrades to #NAME?.

Next, the version-proof option: INDEX MATCH, which does everything VLOOKUP can't and runs in every Excel ever shipped.