VLOOKUP vs XLOOKUP: which should you use?
Quick answer
Use XLOOKUP if you have Microsoft 365 or Excel 2021 or later - it is simpler, safer and more flexible. Use VLOOKUP when you must support older Excel versions, or when following an existing workbook someone else built.
- 1Check which Excel version the file needs to open in.
- 2Use XLOOKUP if Microsoft 365 or Excel 2021+ is available.
- 3Fall back to VLOOKUP with FALSE as the last argument for older versions.
- 4Clean mismatched text first if either formula returns #N/A.
Free, no sign-up, and your file is read on your own device rather than uploaded.
VLOOKUP and XLOOKUP both look up a value in one column and return a matching value from another, but they differ in exactly the ways that cause real spreadsheet mistakes: which direction they can search, what happens by default when nothing matches, and whether inserting a column breaks the formula.
Both are easy to get wrong in the same way - a close-but-not-exact text match (extra spaces, different capitalisation, "Co." vs "Company") returns #N/A from either formula. That is a data-cleaning problem, not a formula problem.
VLOOKUP vs XLOOKUP, side by side
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Looks to the left of the key column | No | Yes |
| Default match type | Approximate (easy to get wrong) | Exact |
| Breaks if you insert a column | Yes (column index number) | No (uses ranges) |
| "Not found" message | #N/A unless wrapped in IFERROR | Built-in argument |
| Returns multiple columns | One at a time | Yes, in one formula |
| Available in Excel 2019 and earlier | Yes | No |
Step by step
Upload both sheets
The sheet you are looking up from and the sheet with the answer column - nothing is sent to a server.
VLOOKUP OnlineChoose the key columns
Pick the column each sheet shares in common - the same role as the first argument in VLOOKUP or XLOOKUP.
VLOOKUP OnlineDownload the matched result
Get a new sheet with the lookup column attached, with no formulas left to maintain.
VLOOKUP Online
Tips
- VLOOKUP is still fine if your file must open in Excel 2019 or earlier.
- It is also fine for a quick one-off lookup with the key in the first column.
- And for following an existing workbook someone else already built around it.
Common problems
#N/A even though the value looks the same.
Look for trailing spaces, numbers stored as text, or different capitalisation or spelling - trim and clean the key columns first.
VLOOKUP returns the wrong row.
The last argument was left as TRUE or omitted. Set it to FALSE for an exact match.
The result broke after a column was inserted.
VLOOKUP uses a fixed column number, so inserting a column shifts it. XLOOKUP uses ranges and is not affected.
Examples
The same lookup, written both ways
The salary for an employee ID in A2, looking in a table where IDs are in column A and salaries in column D (A2:D500) of Sheet2. VLOOKUP: =VLOOKUP(A2, Sheet2!A:D, 4, FALSE) - the 4 counts columns from A, and FALSE forces an exact match (leaving it out, or using TRUE, is the single most common VLOOKUP mistake). XLOOKUP: =XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "Not found") - exact match by default, and the fourth argument sets what shows instead of a bare #N/A. Insert a new column between A and D in Sheet2 afterwards: the VLOOKUP formula now points at the wrong column and has to be fixed by hand; the XLOOKUP formula, built on named ranges rather than a column count, keeps working unchanged.
Frequently asked questions
- Is XLOOKUP better than VLOOKUP?
- In most cases yes. XLOOKUP can look left, defaults to an exact match, returns a clear "not found" value of your choice, and does not break when you insert columns.
- Does XLOOKUP work in older versions of Excel?
- XLOOKUP is available in Microsoft 365 and Excel 2021 and later. Excel 2019 and earlier do not have it, so VLOOKUP is still needed there.
- Can I do a VLOOKUP online without Excel?
- Yes. The VSThiran VLOOKUP tool lets you match two sheets in your browser, with no formulas and no upload to a server.
Related data tools
- VLOOKUP OnlineBring columns from one sheet into another, without writing a formula.
- Match Similar NamesMatch records whose names never quite agree between two systems.
- Compare SpreadsheetsSee what changed between two versions of the same data.
Related articles
- Excel & Data
How to match two spreadsheets without writing a formula
Upload both files, click the matching column in each table (the connector shows what you picked), tick the columns you want copied across, then run it and download the result.
- Excel & Data
Excel names don't match? Find similar records with fuzzy matching
Open Fuzzy Lookup, upload both files, pick the column to match on, and set how close counts as a match. It finds records like "ABC Trading LLC" and "ABC Trading L.L.C." that an exact match would miss, and flags anything too close to call for you to review.
- Excel & Data
How to compare two Excel files and find what changed
Upload both files, pick the column that identifies each row (like an ID), and the comparison shows exactly which rows were added, removed, or changed - and which specific values differ.
Ready to do it?
Free, no sign-up, and nothing is uploaded to a server.