XLOOKUP is the modern replacement for VLOOKUP. Cleaner, more powerful, and easier to write. Here is everything you need.
XLOOKUP searches a column (or row) for a value and returns the corresponding result from another column (or row). It was introduced in Excel 2019 as the official replacement for VLOOKUP, HLOOKUP, and INDEX MATCH — doing everything they do, with a cleaner syntax and fewer limitations.
If you are learning Excel in 2026, start with XLOOKUP. Only learn VLOOKUP so you can understand spreadsheets built by others.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Description |
|---|---|
| lookup_value required | What to search for — a value, text, or cell reference. |
| lookup_array required | The column to search in. |
| return_array required | The column to return the result from. Unlike VLOOKUP, this is a range — not a number. |
| if_not_found optional | What to show when nothing is found. Defaults to #N/A if omitted. |
| match_mode optional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_mode optional | 1 = first to last (default), -1 = last to first, 2/−2 = binary search. |
You only need the first three arguments. XLOOKUP defaults to exact match — no need to add a 0 at the end like VLOOKUP.
=XLOOKUP("Coffee", A2:A100, B2:B100)Search column A for "Coffee" and return the corresponding value from column B.
=XLOOKUP(D2, A2:A100, B2:B100, "Not found")The fourth argument shows a friendly message instead of an ugly #N/A error.
Product codes in column C, names in column A. XLOOKUP can return values to the left.
=XLOOKUP(F2, C2:C100, A2:A100, "Not found")=XLOOKUP(D2, A2:A100, B2:C100)Specify a multi-column return array and XLOOKUP returns all columns for the matched row simultaneously.
=XLOOKUP(D2, A2:A100, B2:B100, "Not found", 0, -1)Search mode -1 searches from last to first — useful when a value appears multiple times and you want the most recent.
=XLOOKUP("*coffee*", A2:A100, B2:B100, "Not found", 2)Match mode 2 enables wildcards. * matches any characters, so "Iced Coffee" and "Coffee Latte" both match.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Look left | ❌ No | ✅ Yes |
| Survives column insertion | ❌ No | ✅ Yes |
| Built-in error handling | ❌ No | ✅ Yes |
| Returns multiple columns | ❌ No | ✅ Yes |
| Works in older Excel | ✅ Yes | ❌ 2019+ only |
| Easier syntax | ❌ Harder | ✅ Easier |
XLOOKUP doesn't exist in Excel 2016 or earlier. If you share files with users on older versions, use VLOOKUP or INDEX MATCH for compatibility.
If lookup_array has 100 rows, return_array must also have 100 rows. Mismatched sizes return a #VALUE! error.
XLOOKUP doesn't distinguish between "coffee", "Coffee" and "COFFEE". For case-sensitive lookup, combine with EXACT in a more complex array formula.
ExcelPro has XLOOKUP exercises across all tracks — real data, instant feedback, free to start.
Try XLOOKUP exercises →