How to Use XLOOKUP in Excel
XLOOKUP is the modern replacement for VLOOKUP — it can look in any direction, handles missing values cleanly, and doesn't break when columns shift. Here's how it works.
XLOOKUP was introduced in Microsoft 365 and Excel 2021 to replace VLOOKUP and HLOOKUP. It's cleaner, more flexible, and fixes every major complaint about VLOOKUP: it looks in any direction, has a built-in default for missing values, and doesn't require counting column numbers.
The syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The first three arguments are required:
- lookup_value — what you're searching for
- lookup_array — the column to search in
- return_array — the column to return from
The last three are optional but useful:
- [if_not_found] — what to show if there's no match (replaces IFERROR wrapping)
- [match_mode] — 0 = exact (default), -1 = exact or smaller, 1 = exact or larger, 2 = wildcard
- [search_mode] — 1 = first to last (default), -1 = last to first, 2 = binary search
Basic example
Look up a product price by product ID:
=XLOOKUP(D2, A2:A100, C2:C100)Same as =VLOOKUP(D2, A:C, 3, FALSE) but without the fragile column number.
With a fallback for missing values
Instead of wrapping in IFERROR, use the fourth argument:
=XLOOKUP(D2, A2:A100, C2:C100, "Not found")Looking to the left
VLOOKUP can't look left — XLOOKUP can. The lookup column and return column are independent:
=XLOOKUP(D2, C2:C100, A2:A100)This returns column A values by searching column C — impossible with VLOOKUP.
Returning multiple columns
XLOOKUP can return an entire row of values by passing a multi-column return array:
=XLOOKUP(D2, A2:A100, B2:D100)This spills results across three columns — Name, Department, and Salary in one formula.
XLOOKUP vs VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Look left | ✗ | ✓ |
| Built-in fallback | ✗ | ✓ |
| Breaks when columns inserted | ✓ breaks | ✗ safe |
| Returns multiple columns | ✗ | ✓ |
| Available in | All versions | 365 / 2021+ only |
→ NOTE
If your workbook is shared with colleagues on older Excel versions, stick with VLOOKUP or INDEX MATCH — XLOOKUP will show as #NAME? on their end.
The Griddy way
The arguments are easy to forget in the right order, especially the optional ones. Just describe what you need:
"For each order, look up the customer's account tier from the Customers sheet using their email address, and show 'Unknown' if the email isn't found"
Griddy picks the right formula and handles the cross-sheet reference automatically.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.