For nearly 30 years, VLOOKUP was the most famous Excel formula in the world. Ask any Excel user to name one formula and VLOOKUP is what they say. But since Microsoft released XLOOKUP in 2019, there's been a genuine debate: should you drop VLOOKUP entirely, or is there still a place for it?
What VLOOKUP Can't Do
- It can only look left-to-right — your lookup column must always be the leftmost in your range.
- You reference columns by number (e.g. column 3), so inserting a new column silently breaks your formula.
- It always returns only one value — no way to pull multiple matching results.
- It defaults to approximate match, which catches many beginners off guard.
Why XLOOKUP Wins
XLOOKUP separates the search range from the return range entirely, meaning you can look in any direction. It defaults to exact match (the safer choice), handles missing values with a built-in if-not-found argument, and can return entire rows or multiple columns in one formula. The syntax is also much more intuitive once you learn it.
Pro Tip: If you're sharing files with colleagues using Excel 2016 or older, stick with VLOOKUP — XLOOKUP won't work on those versions. Always check your team's Excel version before switching.
Our recommendation: learn VLOOKUP well enough to read other people's files, but write all your own lookups with XLOOKUP. It's cleaner, safer, and will serve you better as Excel continues to evolve.
Want to learn this hands-on?
Join a live batch at DataSkills Institute, Hyderabad. Real projects, real trainers, real results.