Formulas
XLOOKUP: When to Use It Instead of VLOOKUP
Look left, return multiple columns, and handle missing values with IFNOTFOUND — XLOOKUP syntax for Excel 365 and Google Sheets.
XLOOKUP replaces most VLOOKUP setups with clearer arguments: what to find, where to find it, what to return, and what to show if nothing matches.
Google Sheets added XLOOKUP in 2022+. Excel 365 / Excel 2021+ have it. Older Excel: stay on VLOOKUP or INDEX/MATCH.
Same task as VLOOKUP — price by SKU
Products sheet: SKU in column B, Price in column E (SKU is not the leftmost column).
=XLOOKUP(A2, Products!B:B, Products!E:E, "Not found")- A2 — SKU on your order row
- Products!B:B — lookup column (does not have to be leftmost)
- Products!E:E — return column
- "Not found" — if SKU missing (optional 4th argument)
VLOOKUP would force you to rearrange columns or use INDEX/MATCH.
Look to the left
Employee ID in column D; name in column A:
=XLOOKUP(D2, Staff!D:D, Staff!A:A)VLOOKUP cannot return a column left of the lookup column.
Return multiple values (Excel 365)
One formula spills name and department:
=XLOOKUP(E2, Products!A:A, Products!B:C)Select the spill range or press Ctrl+Shift+Enter on older dynamic-array builds if needed.
Google Sheets: use two XLOOKUPs or {XLOOKUP(...), XLOOKUP(...)} in adjacent cells if spill is limited.
Match mode and search mode
Exact match (default for most IDs):
=XLOOKUP(E2, Products!A:A, Products!D:D, , 0)5th argument 0 = exact match (same idea as VLOOKUP's FALSE).
Last argument -1 or 1 controls search direction for sorted lists (uncommon for SKUs).
Wrong vs Right
| Wrong | Right |
|---|---|
| Lookup and return ranges different lengths | B:B and E:E both full columns, or same row count like B2:B100 and E2:E100 |
Omitting IFNOTFOUND, then #N/A in customer-facing sheet | , "—" or , 0 as 4th argument |
| Using XLOOKUP on Excel 2016 | Check Formulas → Insert Function — if XLOOKUP missing, use VLOOKUP |
Migration cheat sheet
| VLOOKUP | XLOOKUP |
|---|---|
=VLOOKUP(E2, A:D, 4, FALSE) | =XLOOKUP(E2, A:A, D:D) |
#N/A on miss | 4th arg: "Not found" |
| Column index 3 | Point directly at return column |
After lookups work, summarize with pivot tables.