VLOOKUP vs XLOOKUP: which one should you actually use?

I once spent a whole Sunday afternoon hunting a bug in a sales report that turned out to be one inserted column. One column. The VLOOKUP didn't error out — it just silently started returning the wrong field. Region instead of revenue. Nobody noticed for six weeks. By the time I caught it the team had made two pricing decisions on garbage numbers. Fun times.

That's the thing about VLOOKUP. It doesn't fail loudly. It fails quietly, in the background, while you're none the wiser. XLOOKUP, which Microsoft shipped in 2019 and is now bog-standard in Microsoft 365 and Excel 2021+, fixes this and basically every other thing that's annoying about VLOOKUP. So why are people still teaching VLOOKUP first? Habit, mostly. Let's fix that.

The 30-second comparison

Feature	VLOOKUP	XLOOKUP
Look left of the lookup column	No	Yes
Default to exact match	No (defaults to approximate)	Yes
Custom "not found" value	No (returns #N/A)	Yes (built-in argument)
Search bottom-to-top	No	Yes
Survives column insertion	No (column index is positional)	Yes (you reference real ranges)
Returns whole row easily	Awkward, needs tricks	Yes (return a range)
Wildcard / regex search modes	Wildcards only	Wildcards and regex
Available in older Excel	Yes (all versions)	Microsoft 365 / 2021+ only

A plain VLOOKUP, for old times' sake

=VLOOKUP("Widget A", A2:D100, 3, FALSE)

Translation: find "Widget A" in the first column of A2:D100, then return what's in the third column (column C) for that row. The FALSE is critical. Forget it and you get an approximate match — which is the source of roughly 80% of the mysterious VLOOKUP bugs I've debugged in client spreadsheets over the years. It works. Until it doesn't. Then it returns whatever happened to be near your lookup value, alphabetically.

Three things baked into that one formula are bad design:

You can only look right. The lookup column has to be the first column. If your IDs are in column C and you want data from column A, tough luck — rearrange the sheet, or wrap the whole thing in CHOOSE, or give up and use INDEX/MATCH.
The "3" is positional. This was my Sunday afternoon. Insert a column anywhere between A and D and the formula now points at a different field. Silently.
The default is approximate match. Microsoft made the wrong default in 1985 and we've been paying for it ever since. Wild that nobody fixed it for 34 years.

XLOOKUP — the same idea, no booby traps

=XLOOKUP("Widget A", A2:A100, C2:C100)

Lookup value. Column to search. Column to return. That's it. No counting columns. No FALSE flag. No fragile range that breaks the moment a colleague inserts a column. Default behavior is exact match — which is what almost everyone wants almost all the time anyway.

Want a fallback when nothing matches?

=XLOOKUP("Widget A", A2:A100, C2:C100, "Not found")

That fourth argument replaces the ugly IFERROR(VLOOKUP(...), "Not found") wrapper everyone used to type. One formula. One job.

Looking left

Here's where you'll grin the first time. Say product names live in column C and product IDs are in column A, and you want the ID for "Widget A":

=XLOOKUP("Widget A", C2:C100, A2:A100)

Done. Try that in VLOOKUP without rearranging columns or doing acrobatics. It's possible — CHOOSE arrays, INDEX/MATCH wrappers — but it's ugly and nobody who reads your formula six months from now will know what you were thinking. With XLOOKUP, the lookup range and return range are independent. They don't even have to be next to each other.

Returning more than one column

Both can do it. XLOOKUP just doesn't make you fight for it:

=XLOOKUP("Widget A", A2:A100, C2:E100)

This spills the values from columns C, D, and E for the matching row across three cells. Assuming you're on a Microsoft 365 build with dynamic arrays, which means basically anything from 2020 onward. VLOOKUP needs a separate formula per column or some array trickery I refuse to type out here.

search_mode and match_mode — the optional bits

XLOOKUP returns the first match by default. If you want the last match — the classic "most recent transaction by customer" lookup — set the optional sixth argument:

=XLOOKUP("Widget A", A2:A100, C2:C100, "Not found", 0, -1)

Arguments in order: lookup value, lookup array, return array, if-not-found, match mode, search mode. The fifth (0) is exact match — default, you can leave it out. The sixth (-1) flips the search to bottom-up.

Match modes worth filing away:

0 — exact match (default)
-1 — exact, or next smaller item if not found
1 — exact, or next larger item if not found
2 — wildcard match (use * and ? in the lookup value)

Search modes:

1 — first to last (default)
-1 — last to first
2 — binary search ascending (way faster on huge sorted lists)
-2 — binary search descending

You'll never touch arguments five and six for everyday work. They're there when you genuinely need them. The "last match" trick alone has saved me hours over the years — it's the cleanest way to get "most recent value per key" without writing a SUMIFS or pivoting the data.

When you'd still write a VLOOKUP in 2026

There are exactly three legit reasons:

The file is going to someone on Excel 2019 or older, or some standalone Office install that hasn't been updated in years. XLOOKUP returns #NAME? on those versions.
You're patching a legacy spreadsheet where every other formula is a VLOOKUP and consistency matters more than improvement. (My personal rule: if the sheet is being retired in three months, leave it alone.)
You're explaining a concept to someone who only knows VLOOKUP and needs to maintain what you write.

For everything else — anything new, any automation, anything you'd build today — XLOOKUP. Stop typing VLOOKUP. Just stop.

Pitfalls that bite both formulas

A few classics that'll trip you up regardless:

Numbers stored as text. If your lookup is the number 1234 but the column has the text "1234", you get #N/A. Spot it by selecting a cell — text aligns left, numbers right by default. Wrap one side in VALUE() or the other in TEXT().
Trailing spaces. "Widget A" and "Widget A " are not the same to Excel and never will be. Wrap suspicious lookups in TRIM().
Mixed case. Both VLOOKUP and XLOOKUP ignore case. If you genuinely need case-sensitive matching, you're in array-formula-with-EXACT() territory. Annoying but doable.
#SPILL! errors with XLOOKUP. If the return range is multiple columns and there's already data sitting where the spill wants to land, you get #SPILL!. Clear the cells. Excel won't overwrite for you and that's actually the right call.
Volatile recalc on huge sheets. XLOOKUP is faster than VLOOKUP at exact matches on unsorted data. But on sorted data with millions of rows, binary-search VLOOKUP (TRUE as the fourth argument, sorted lookup column) can still beat it. Edge case for almost everyone.

What about INDEX/MATCH and FILTER

Two honourable mentions worth knowing about:

INDEX/MATCH was the power-user workaround for two decades. It does almost everything XLOOKUP does, just with two functions instead of one. Looks like =INDEX(C2:C100, MATCH("Widget A", A2:A100, 0)). If you see it in old workbooks, leave it alone — it works fine. Don't write new ones.

FILTER is what you actually want when you need multiple matching rows, not just the first or last. =FILTER(A2:D100, B2:B100="Widget A") gives you every row where column B matches. XLOOKUP returns one row. FILTER returns all of them. Different tools for different jobs. Mixing them up is one of the most common things I see clients do.

If you're teaching someone Excel today

Skip VLOOKUP entirely. I'm serious. Start them on XLOOKUP. They'll thank you in six months when they're not debugging an approximate-match disaster, and again the first time someone inserts a column in the source data and nothing breaks. The only reason VLOOKUP is still the default in most Excel courses is institutional inertia. The course material was written before XLOOKUP existed and nobody's updated it.

I had a junior analyst at one client who'd only ever used XLOOKUP. When I pulled up an old VLOOKUP sheet to explain a bug, she squinted at the formula for a minute and asked "wait, why is there a number in there?" Honestly? She was right to ask.

So

Use XLOOKUP for any new formula on Microsoft 365 or Excel 2021+. Defaults to exact match. Looks left. Built-in not-found value. Survives column inserts. Save VLOOKUP for compatibility cases or legacy maintenance. Reach for FILTER when you need multiple matches.

And if you're still using approximate-match VLOOKUPs in 2026 — please. For me. Stop.