VLOOKUP vs XLOOKUP: which one should you actually use?

If you've used Excel for more than five minutes, you've met VLOOKUP. And if you've used it for more than a year, you've cursed at it. XLOOKUP, introduced in 2019 and now standard in Microsoft 365, fixes most of what makes VLOOKUP painful.

The 30-second comparison

Feature	VLOOKUP	XLOOKUP
Look left	❌	✅
Default to exact match	❌ (defaults to approximate!)	✅
Custom "not found" value	❌	✅
Search bottom-to-top	❌	✅
Returns whole row easily	Awkward	✅
Available in older Excel	✅	Microsoft 365 / 2021+

VLOOKUP example

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

This says: find "Widget A" in the first column of A2:D100, then return the 3rd column. The FALSE is critical — leave it off and you get an approximate match, which is the source of about 80% of mysterious VLOOKUP bugs.

XLOOKUP — same thing, cleaner

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

You pass the lookup value, the column to search, and the column to return. No counting columns, no FALSE flag. If you want a fallback for missing values:

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

When you'd still pick VLOOKUP

• You're sharing the file with someone on Excel 2019 or older, or non-Microsoft-365 users.
• You're maintaining a legacy spreadsheet where consistency with existing formulas matters.

For everything else — new spreadsheets, automations, anything you'd build today — use XLOOKUP.

A common gotcha

XLOOKUP returns the first match by default. If you want the last match (useful for "most recent transaction" lookups), set the optional search_mode argument to -1:

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

That sixth argument flips the search to bottom-up.

Worth memorizing

If you teach someone Excel today, skip VLOOKUP and start them on XLOOKUP. They'll thank you in six months when they don't have to debug an approximate-match disaster.