XLOOKUP vs VLOOKUP — Which Should You Use Today?
Only examples reproduced directly in Excel 365 are included, and AI was used as an aid in organizing the draft.
Related guides (internal links)
1) Key comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default exact/approximate match | Approximate (optional) → prone to mistakes | Exact (default) |
| Left lookup | Not available (right only) | Available |
| Reliability when columns move | Column number breaks | More reliable because it uses range references |
| Multi-column return | Not available | Available with spill |
| When no match is found | Must be wrapped in IFERROR | Specify a message with the fourth argument |
| Search mode | Limited | Supports first/last match and wildcards |
2) XLOOKUP quick start
=XLOOKUP(H2, Codes[Code], Codes[Name], "Code not found")TIP Multi-column return: =XLOOKUP(H2, Codes[Code], CHOOSECOLS(Codes,2,3,4), "Not found") → description, price, and category all spill at once.
3) Six practical cases (ready to copy and paste)
① Left lookup
=XLOOKUP(H2, RightTable[Code], LeftTable[Name])② Last match
=XLOOKUP(H2, Hist[Code], Hist[Price], , 0, -1)③ Partial text (wildcard)
=XLOOKUP("*"&H2&"*", Items[Name], Items[Code], "Not found", 2)④ Standardize error messages across a range
=IFERROR(XLOOKUP(H2, Codes[Code], Codes[Name]), "Unregistered code")⑤ Multiple criteria
=XLOOKUP(1, (Tbl[Code]=H2)*(Tbl[Region]=H3), Tbl[Price])⑥ Reference an entire spill range
=A2#4) Error handling and performance
- Reference Table columns to stay safe when columns are moved.
- For large datasets, preprocess with Power Query before looking up values.
- Standardize error messages with
IFERROR.
5) FAQ
Can I automatically convert VLOOKUP to XLOOKUP?
Manual replacement is safer. Split the same range into lookup_array and return_array when entering the formula.
What if I need an approximate match?
Use match_mode=1, as in =XLOOKUP(value, array, ret, , 1), and sort the array in ascending order.