The Short Answer
If you have Excel 365, Excel 2021, or Google Sheets (2022+) — use XLOOKUP. It is more powerful, cleaner to write, and fixes every major limitation of VLOOKUP.
If your file needs to open in Excel 2019 or older — use VLOOKUP. XLOOKUP is not available in those versions and will return a #NAME? error.
Side-by-Side Comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Look left of lookup column | ❌ No — lookup column must be leftmost | ✅ Yes — any direction |
| Error handling | Requires IFERROR wrapper | Built-in [if_not_found] argument |
| Breaks when columns inserted | ✅ Yes — col_index_num shifts | ❌ No — uses ranges, not positions |
| Return multiple columns | ❌ One column only | ✅ Yes — select a multi-column range |
| Wildcard matching | Partial support | Built-in with match_mode=2 |
| Availability | All Excel versions, all Sheets | Excel 365, Excel 2021, Sheets 2022+ |
Example 1: Basic Lookup — Same Task, Different Syntax
Fetching a price from a product table where the product ID in column A matches A2:
VLOOKUP version
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not found")
XLOOKUP version — cleaner
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")
The XLOOKUP version is shorter, does not need IFERROR, and will not break if you insert a column between A and B in Sheet2.
Example 2: Left Lookup — XLOOKUP Only
Returning column A (to the LEFT of the lookup column C):
VLOOKUP — cannot do this
=VLOOKUP(D2, A:C, -2, FALSE) ← #VALUE! error — negative col_index not allowed
XLOOKUP — works perfectly
=XLOOKUP(D2, C:C, A:A, "Not found") ← returns column A for the matching row
Example 3: Multiple Conditions
XLOOKUP with two conditions (AND logic)
=XLOOKUP(E2&F2, A:A&B:B, C:C, "Not found")
VLOOKUP equivalent — requires helper column
=IFERROR(VLOOKUP(E2&F2, D:E, 2, FALSE), "Not found")
When to Keep Using VLOOKUP
- Your spreadsheet must open in Excel 2019 or earlier (or Excel Online in certain configurations)
- You are collaborating with users who have older Excel versions
- The formula is very simple and you just need a quick lookup to the right
- You are maintaining existing formulas that already use VLOOKUP and work correctly
Pro tip: Use ExcelForm's generator — just describe your lookup and it automatically picks VLOOKUP or XLOOKUP based on your selected Excel version. No need to remember the syntax for either.
FAQ
Frequently Asked Questions
Is XLOOKUP available in all versions of Excel?▾
XLOOKUP is available in Excel 365, Excel 2021, and Google Sheets (2022+). It is NOT available in Excel 2019, Excel 2016, or Excel 2013. If you open an XLOOKUP formula in an older version, it shows a #NAME? error.
Can I use XLOOKUP in Google Sheets?▾
Yes. XLOOKUP was added to Google Sheets in 2022 with identical syntax to Excel 365. If you are on an older Sheets version or unsure, use INDEX MATCH instead — it works in all versions of both Excel and Sheets.
Does XLOOKUP replace INDEX MATCH too?▾
For most tasks yes. XLOOKUP is simpler to write than INDEX MATCH and handles most lookup scenarios including left lookups and multi-column returns. INDEX MATCH is still preferred for two-way lookups (matching both a row and a column), which XLOOKUP does not natively support.
How do I generate VLOOKUP or XLOOKUP formulas automatically?▾
Use ExcelForm at excelform.xyz — describe your lookup in plain English, select your Excel version, and it generates the correct formula automatically. Free, no signup required.