VLOOKUP vs XLOOKUP in Google Sheets
Short answer: Use XLOOKUP for new sheets — it searches in any direction, returns a clean fallback for missing values, and doesn't break when you insert columns. Keep VLOOKUP only for older sheets or when someone must open the file in software without XLOOKUP.
Copy the one most people want (XLOOKUP)
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")
Looks up A2 in column A and returns the matching value from column C, or "Not found" if there's no match — no IFERROR wrapper needed.
VLOOKUP vs XLOOKUP — the differences that matter
| Aspect | VLOOKUP | XLOOKUP |
|---|---|---|
| Lookup direction | Left-to-right only — the key column must sit left of the result. | Any direction — the result column can be left, right, above or below. |
| Missing value | Returns #N/A; you must wrap it in IFERROR. | Has a built-in 4th argument for the "if not found" text. |
| Column index | You pass a hard number (e.g. 3). Insert a column and it points at the wrong data. | You point at the actual result range, so inserting columns is safe. |
| Approximate match | 4th argument TRUE/FALSE; easy to forget and get wrong matches. | Explicit match mode (0 exact, 1/-1 next larger/smaller, 2 wildcard). |
| Availability | Everywhere, including very old spreadsheets. | Modern Google Sheets and Excel 365; not in legacy files. |
Which one should you use?
Use VLOOKUP when…
You're editing a sheet that already uses VLOOKUP everywhere, or the file must open in old software that lacks XLOOKUP. It's also fine for a quick one-off lookup where the layout won't change.
Use XLOOKUP when…
You're building anything new, the value you want sits to the left of the key, you'll be inserting or moving columns, or you want a tidy "Not found" message without an extra IFERROR.
Same task, both formulas
Look up an email in column A and return the person's department from column C.
=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found")
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")
Same result — but XLOOKUP survives you inserting a column between A and C, while VLOOKUP's hard-coded 3 would suddenly return the wrong column.
FAQ
Is XLOOKUP faster than VLOOKUP in Google Sheets?
On normal sheets the speed difference is negligible. XLOOKUP's advantage is reliability and readability, not raw speed.
Can XLOOKUP look to the left?
Yes — that's its headline advantage. VLOOKUP can only return columns to the right of the key; XLOOKUP can return any column.
Should I rewrite all my VLOOKUPs?
No need. Working VLOOKUPs are fine. Switch to XLOOKUP for new formulas and when you're already editing a fragile one.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas