Getting Started With XLOOKUP in Excel
It fixes the two things that always made VLOOKUP fragile
By Shehryar Hassan, Microsoft 365 & Azure Consultant
If you've spent years typing VLOOKUP formulas and squinting at column index numbers, you might not have noticed that Excel replaced it with something better. XLOOKUP has been in Microsoft 365 versions of Excel for a while now, and once you get used to it, going back to VLOOKUP feels like a step backward.
Why it beats VLOOKUP
VLOOKUP only searches left to right, and it breaks the moment someone inserts a column in the middle of your range, because it counts columns by number. XLOOKUP fixes both problems. You point it at the column you're searching and the column you want returned, by reference, not by counting. It doesn't care if your columns move around later.
The basic formula looks like this:
=XLOOKUP(lookup_value, lookup_array, return_array)
So if you're pulling an employee's department from a list based on their ID, it's something like =XLOOKUP(A2, Employees!A:A, Employees!C:C). No counting columns, no fourth argument for exact match, since XLOOKUP defaults to exact match, which is what most people actually want anyway.
A couple of things worth knowing
XLOOKUP has a built in "if not found" argument, so instead of a formula returning #N/A and breaking the rest of your spreadsheet, you can have it return "Not found" or a blank cell. Add it as the fourth argument: =XLOOKUP(A2, Employees!A:A, Employees!C:C, "Not found").
It can also search bottom to top if you need the last match instead of the first, which used to require a much clunkier formula built out of INDEX and MATCH.
One catch: XLOOKUP only works in Excel for Microsoft 365 and Excel 2021 or later. If you're sharing a workbook with someone still on Excel 2016 or 2019, your formulas will show up as #NAME? errors on their end. It's worth checking what version your team is on before you build a shared workbook around it.
If you're already comfortable with VLOOKUP, you don't need to relearn much. The logic is the same, there's just less to remember and fewer ways for it to quietly break when someone edits the sheet six months from now.
Get new posts by email
One note when there is something worth reading. No spam, unsubscribe anytime.
Related articles
What Clipchamp Is and Why It's Suddenly on Your Windows Start Menu
Clipchamp didn't require an install, it just appeared on people's machines. Here's what it actually is, what a Microsoft 365 subscription adds to it, and when it's worth reaching for instead of a real editing tool.
Why Your Excel Conditional Formatting Rules Stop Working
If your conditional formatting rules used to work and now don't, the cause is almost always overlapping rules left behind from copy and paste, not a broken feature.
Why Your Word Document Opens in Protected View (and When It's Safe to Turn Off)
Word's Protected View isn't a glitch, it's a safety check for files that come from outside your trusted circle. Here's why it happens and when it's actually fine to click through it.