XLOOKUP vs VLOOKUP: Differences and Which to Use
XLOOKUP vs VLOOKUP side by side: syntax, default match type, looking left, inserted columns, speed and compatibility. A clear rule for which one to use.
By Ayoub Farid · · 2 min read
Both functions find a value in a table. XLOOKUP is newer and fixes most of VLOOKUP's weak spots, but VLOOKUP still works everywhere. Here's the difference in one table, then a simple rule.
Side by side
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Syntax | =VLOOKUP(F2,A2:C100,3,FALSE) | =XLOOKUP(F2,A2:A100,C2:C100) |
| Default match | Approximate (risky) | Exact |
| Look left | No | Yes |
| Return column | Number you count | Range you point at |
| Inserted column | Can break it silently | Keeps working |
| Not-found message | Needs IFERROR | Built in |
| Search from bottom | No | Yes |
| Return several columns | One at a time | Yes |
| Excel 2019 and older | Yes | No (#NAME?) |
| Google Sheets | Yes | Yes |
The same lookup, both ways
Find the price for code F2:
=VLOOKUP(F2,$A$2:$C$100,3,FALSE)
=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100)With a not-found message:
=IFERROR(VLOOKUP(F2,$A$2:$C$100,3,FALSE),"Not found")
=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")Why XLOOKUP is safer
- Exact match by default. Forgetting VLOOKUP's FALSE is one of the most common causes of wrong answers. See VLOOKUP not working.
- No column numbers. Insert a column in the table and VLOOKUP's "3" may now point at the wrong data, with no error. XLOOKUP points at the column itself.
- Looks in any direction. No rearranging tables.
When VLOOKUP is still the right choice
- The file will be opened in Excel 2019 or earlier.
- A job test or template specifically asks for it.
- You're editing existing VLOOKUP formulas and consistency matters.
The rule
Use XLOOKUP for new work in Microsoft 365, Excel 2021+ or Google Sheets. Know VLOOKUP for compatibility and job tests. If you need to support old versions and look left, use INDEX MATCH.
Frequently asked questions
Is XLOOKUP replacing VLOOKUP?
Microsoft recommends XLOOKUP for new work, but VLOOKUP still exists and works for compatibility.
Is XLOOKUP faster than VLOOKUP?
Speed differences are small for normal-sized sheets. The main benefits are accuracy and simplicity.
Will XLOOKUP work for someone with Excel 2016?
No. They'll see #NAME?. Use VLOOKUP or INDEX MATCH for shared files.
Should I learn VLOOKUP first?
Learn whichever your workplace uses. Many job tests still ask for VLOOKUP, so it's worth knowing both. Start with VLOOKUP or XLOOKUP.