XLOOKUP vs VLOOKUP: which should you use?
VLOOKUP has been the go-to Excel lookup for decades, but XLOOKUP fixes its most annoying limitations. Here’s the honest comparison — what XLOOKUP does better, the exact syntax, and the few cases where VLOOKUP still has a place.
The short answer
If you have XLOOKUP, use it — with a couple of exceptions.
If your Excel has XLOOKUP (Microsoft 365 and Excel 2021+), it’s the better choice for almost every lookup. It fixes the three things that make VLOOKUP fragile: it can look to the left as well as the right, it defaults to an exact match (VLOOKUP’s default approximate match is a classic source of silent errors), and it references the return column directly instead of by a counted index number that breaks when you insert a column.
VLOOKUP still has a place: sheets shared with people on older Excel versions that don’t have XLOOKUP, and simple legacy workbooks you don’t want to rewrite. But for new work, XLOOKUP is more robust and more readable.
XLOOKUP vs VLOOKUP at a glance
The differences that actually bite.
| Capability | XLOOKUP | VLOOKUP |
|---|---|---|
| Look to the left | Yes | No (return must be right of key) |
| Default match | Exact | Approximate (a common bug) |
| Return column | Referenced directly | By counted index number |
| Breaks if a column is inserted | No | Yes (index shifts) |
| Return multiple columns | Yes (spills) | No |
| Availability | Excel 2021 / 365+ | All versions |
The syntax, side by side
XLOOKUP reads more like the question you’re asking.
VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) — you must remember to add FALSE for an exact match, and count which column number holds the answer.
XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array) — you point at the column to search and the column to return, exact match is automatic, and there’s an optional argument for a ‘not found’ message instead of #N/A. It’s shorter and doesn’t break when the sheet layout changes.
When you’re really joining data, not looking up
If you’re combining files, a lookup isn’t the right tool.
Both functions are for pulling one matching value into a row. If what you’re actually doing is combining two datasets on a shared key — this month’s file with last month’s, or orders with customers — a lookup formula gets fragile fast across thousands of rows.
That’s a join, and it’s where a tool helps. DataHub Pro blends files on a key column and can even show you exactly what changed between two versions — no XLOOKUP gymnastics, and the result is auditable. Upload two files and see it in about a minute.
Frequently asked questions
What’s the difference between XLOOKUP and VLOOKUP?
XLOOKUP can look to the left as well as the right, defaults to an exact match, and references the return column directly instead of by a counted index number. VLOOKUP can only return a value to the right of the key, defaults to an approximate match, and breaks if you insert a column. XLOOKUP is more robust for new work.
Should I use XLOOKUP or VLOOKUP?
If you have Excel 2021 or Microsoft 365, use XLOOKUP for almost everything — it’s more robust and readable. Keep VLOOKUP for sheets shared with people on older Excel versions that lack XLOOKUP, or simple legacy workbooks you don’t want to rewrite.
Why is XLOOKUP better than VLOOKUP?
Three reasons: it can look left, it defaults to exact match (avoiding VLOOKUP’s approximate-match bug), and it references the return column directly so inserting a column doesn’t break it. It also returns a friendly ‘not found’ message and can spill multiple columns.
Is VLOOKUP being removed from Excel?
No. VLOOKUP remains in Excel for backward compatibility and still works fine. XLOOKUP is the newer, more capable function Microsoft recommends for new work, but VLOOKUP isn’t going away.
Does XLOOKUP work in older Excel?
XLOOKUP is available in Excel 2021 and Microsoft 365. Older versions (2019 and earlier) don’t have it, so if you share workbooks with people on older Excel, VLOOKUP or INDEX/MATCH is safer for compatibility.
What if I need to combine two files, not just look up a value?
That’s a join, not a lookup, and lookup formulas get fragile across large files. A tool like DataHub Pro blends two files on a shared key and can show what changed between versions, with an auditable result — no XLOOKUP required.
Explore related guides
More Excel function and how-to guides.
Beyond lookups: analyse the whole file
Upload your spreadsheet and get dashboards, forecasts and auditable answers — and blend two files on a key without wrestling with lookup formulas. Free tier, then $14.99/mo.
Try it free on your file →