VLOOKUP has been Excel’s go-to lookup function for decades. XLOOKUP is its modern replacement. Both find a value and return related data, but they work quite differently.
Short answer: use XLOOKUP if everyone who opens your file has Excel 365, Excel 2021 or later, or Google Sheets. Use VLOOKUP only when you need compatibility with older Excel versions.
The same lookup, written both ways
Say you have product codes in column A, names in column B and prices in column C, and you want the price for the code in E2.
VLOOKUP:
=VLOOKUP(E2, A2:C100, 3, FALSE)XLOOKUP:
=XLOOKUP(E2, A2:A100, C2:C100)VLOOKUP needs the whole table and a column number. XLOOKUP just needs the column to search and the column to return. That small difference solves most of VLOOKUP’s problems.
Side-by-side comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match type | Approximate (risky) | Exact |
| Look to the left | No | Yes |
| Breaks when you insert a column | Yes | No |
| Built-in “not found” message | No (needs IFNA) | Yes |
| Search from bottom to top | No | Yes |
| Return several columns at once | No | Yes |
| Works in Excel 2019 and older | Yes | No |
| Works in Google Sheets | Yes | Yes |
Difference 1: Exact match by default
If you forget VLOOKUP’s last argument, it uses approximate match and can quietly return the wrong value. XLOOKUP uses exact match unless you ask for something else, so this mistake can’t happen.
Difference 2: XLOOKUP can look left
VLOOKUP only searches the first column of a range and returns values to the right. If you want to find a product by its name (column B) and return its code (column A), VLOOKUP can’t do it.
XLOOKUP doesn’t care about column order:
=XLOOKUP(E2, B2:B100, A2:A100)Difference 3: Inserting columns doesn’t break XLOOKUP
VLOOKUP uses a hard-coded column number, such as 3. If someone inserts a new column inside the table, the price is now in column 4, but the formula still says 3, so it returns the wrong data.
XLOOKUP points directly at the return column, so Excel adjusts the reference automatically.
Difference 4: Built-in “not found” value
With VLOOKUP, you need IFNA to replace #N/A:
=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found")XLOOKUP has a fourth argument for this:
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")Difference 5: Search from the bottom
If a value appears several times, VLOOKUP always returns the first match. XLOOKUP can search from the last row up, which is handy for finding the most recent entry, like a customer’s latest order:
=XLOOKUP(E2, A2:A100, C2:C100, "Not found", 0, -1)The 0 means exact match, and -1 means search from last to first.
Difference 6: Return several columns at once
XLOOKUP can return a whole row of results. This formula returns both the product name and price, spilling into two cells:
=XLOOKUP(E2, A2:A100, B2:C100)With VLOOKUP you’d need two separate formulas.
When VLOOKUP is still the right choice
- You share files with people using Excel 2019, 2016 or older. XLOOKUP shows a #NAME? error there.
- You’re editing an existing workbook full of VLOOKUP formulas and want to keep it consistent.
- You’re preparing for a job test or interview that specifically asks about VLOOKUP.
What about INDEX and MATCH?
Before XLOOKUP, advanced users combined INDEX and MATCH to look left and avoid the column-number problem. It still works in every version of Excel, so it’s a good choice when you need XLOOKUP’s flexibility in an older version.
Our recommendation
- New workbooks in Excel 365 or Google Sheets: use XLOOKUP.
- Files shared with older Excel versions: use VLOOKUP or INDEX/MATCH.
- Learning Excel: learn both. You’ll read VLOOKUP in other people’s files for years.
Want to go deeper? Read VLOOKUP Explained Simply, our XLOOKUP beginner guide, and how to fix #N/A errors in both functions.