XLOOKUP
What it's for
XLOOKUP is Microsoft's newer answer to VLOOKUP — built to fix the exact frustrations VLOOKUP users ran into for years. It does the same core job (find a value, return related information), but with fewer rules to remember.
When you'd use it
If you have access to it (available in Microsoft 365 and Excel 2021 or newer), XLOOKUP is generally the better default over VLOOKUP for new spreadsheets, since it avoids several of VLOOKUP's most common gotchas without needing INDEX/MATCH's two-formula setup.
How it works
XLOOKUP asks three questions, and that's usually all you need: (1) What am I searching for? (2) Which column should I search through? (3) Which column should I pull my answer from? That's the entire difference from VLOOKUP worth noticing: there's no column-counting step, and no direction requirement. You point directly at the column you want the answer to come from, whether it's to the left or right of where you searched.
A worked example
=XLOOKUP("E1042", A2:A500, B2:B500)Read left to right: search for E1042 in this first range, and give me the answer from this second range, in the same row it was found.
A tip worth knowing
Because XLOOKUP doesn't need an exact-match setting the way VLOOKUP does (it defaults to exact match automatically), it's harder to accidentally get a rough, misleading match instead of a precise one — one of the more common mistakes people run into with VLOOKUP.
Have a formula of your own you're stuck on?
Paste it and get an explanation →