XLOOKUP Basics
VLOOKUP's column counting, left-only lookups, and broken-by-inserted-column fragility have annoyed spreadsheet users for decades. XLOOKUP fixes all of it in one function: look any direction, exact match by default, graceful not-found handling. This guide makes it your default lookup.
TL;DR / Quick Fix
Quick fix: XLOOKUP replaces VLOOKUP — in a cell type =XLOOKUP(lookup_value, lookup_array, return_array) and it finds the match without column counting. It looks left and right, defaults to exact match, and returns a custom value when nothing is found. One function, far fewer headaches.
Follow these steps
- Learn the syntax. =XLOOKUP(lookup_value, lookup_array, return_array). Three arguments do what VLOOKUP needed four-plus gymnastics for — no column index counting.
- Look left and right freely. Unlike VLOOKUP, the return array can sit anywhere relative to the lookup array. Leftward lookups just work — the most liberating change.
- Trust the exact match default. XLOOKUP defaults to exact match; VLOOKUP defaulted to approximate (the source of countless silent errors). One fewer trap for beginners.
- Handle not-found gracefully. Add the fourth argument: =XLOOKUP(..., "Not found"). No more IFERROR wrapping for the common case — clean formulas, clean sheets.
- Compare with the old guard. Still maintaining VLOOKUP sheets? Our VLOOKUP vs XLOOKUP comparison helps you decide what to convert and what to leave.
- Mind compatibility. XLOOKUP needs modern Excel (Microsoft 365 / 2021+). Sharing with Excel 2019 or older? Stick to VLOOKUP or INDEX-MATCH for those files.
What NOT to do
- Do not use XLOOKUP in files for old Excel. It shows as #NAME? on versions that lack it — know your audience's Excel version.
- Do not forget the match mode. Exact is default, but wildcards and binary search modes exist for special cases.
- Do not nest XLOOKUPs blindly. One clean lookup beats three nested ones — restructure the data instead.
- Do not skip the not-found argument. A graceful 'Not found' beats #N/A cascading through dependent formulas.
Frequently asked questions
Is XLOOKUP faster than VLOOKUP?
Comparable for typical use; its real wins are flexibility and safety, not raw speed.
Can XLOOKUP return multiple columns?
Yes — give it a multi-column return array and it spills the results. Dynamic arrays do the rest.
Should I convert all old VLOOKUPs?
Working sheets: leave them. New work and broken lookups: XLOOKUP. Convert on touch, not on principle.