XLOOKUP vs VLOOKUP: which should you use?
Both find a value in one column and return something from the same row. XLOOKUP is the modern replacement; VLOOKUP still matters because it’s everywhere in older files and on Excel tests.
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Looks left | Yes | No, the key must be the first column |
| Default match | Exact | Approximate (you must add FALSE) |
| Survives inserted columns | Yes | No, the column number goes stale |
| Built-in “not found” message | Yes, 4th argument | No, wrap in IFERROR / IFNA |
| Search from the bottom | Yes, search_mode -1 | No |
| Available in | Microsoft 365, Excel 2021+, Google Sheets | Every version of Excel and Sheets |
Side by side
XLOOKUP
=XLOOKUP(1006,A2:A9,C2:C9) → Omar
VLOOKUP
=VLOOKUP(1006,A2:F9,3,FALSE) → Omar
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Which should you use?
Use XLOOKUP whenever everyone who opens the file has Excel 2021 or Microsoft 365. Learn VLOOKUP anyway: you’ll inherit spreadsheets full of it, and interviewers still ask about it.
Learn more
- XLOOKUPSearches one column and returns the matching value from another.
- VLOOKUPFinds a row by its first column and returns another column.
- INDEX/MATCH vs VLOOKUPINDEX MATCH vs VLOOKUP: the difference explained
- SUMIF vs SUMIFSSUMIF vs SUMIFS: one condition or many
- COUNTIF vs COUNTIFSCOUNTIF vs COUNTIFS: counting with conditions