How to Use SUMIFS to Analyse Data Like a Pro
If you’re still using SUM and IF separately to crunch numbers in Excel, it’s time to level up. SUMIFS is one of Excel’s most powerful functions for analysing data — and it’s surprisingly eas...
Read MoreIf you’ve been using Excel for years, you’ve probably tangled with VLOOKUP, wrestled with HLOOKUP, and maybe even constructed the occasional INDEX/MATCH combo when things got serious. But now, Microsoft has dropped a game-changer—XLOOKUP—and it’s hands-down the most powerful lookup function we’ve seen in Excel’s long history.
So, What Makes XLOOKUP So Superior?
Let’s break it down and see why XLOOKUP deserves the top spot on your formula cheat sheet:
1. No More Guessing Column Numbers
With VLOOKUP, you had to count columns (sometimes painfully) to get your return value. If your table changed, the formula broke. XLOOKUP lets you reference return columns directly by range—no more counting, no more broken formulas.
=XLOOKUP(B2, A2:A100, D2:D100)Compare that to:
=VLOOKUP(B2, A2:D100, 4, FALSE)One of these is intuitive, and the other feels like a game of Excel roulette.
2. Left-Lookups? Finally!
VLOOKUP can only search from left to right. Need to grab a value to the left of your lookup column? Too bad—unless you used INDEX/MATCH. XLOOKUP breaks that barrier. It works in any direction—left, right, up, down. It’s like giving your lookup function a GPS.
3. One Function to Rule Them All
With XLOOKUP, there’s no need for both VLOOKUP and HLOOKUP. One function handles vertical and horizontal lookups seamlessly. It even replaces INDEX/MATCH in most scenarios—with far better readability and fewer moving parts.
4. Built-In Error Handling
Remember wrapping your lookups in IFERROR or IFNA to catch missing values?
=IFERROR(VLOOKUP(B2, A2:B100, 2, FALSE), "Not Found")Now compare it to this:
=XLOOKUP(B2, A2:A100, B2:B100, "Not Found")Cleaner, simpler, and way easier to teach and maintain.
5. Exact Match by Default
This might seem small, but it’s a big win. VLOOKUP defaults to an approximate match unless you explicitly set it to FALSE. That leads to hidden errors. XLOOKUP defaults to exact match—which is what you wanted 95% of the time anyway.
6. Reverse Lookups and More
Want to look something up from bottom to top? Or do a binary search on sorted data? XLOOKUP supports reverse searches, wildcard matching, and custom sort modes. It’s like VLOOKUP went away and got a Master’s degree.
Goodbye Legacy, Hello Logic
XLOOKUP simplifies complex formulas, reduces errors, and improves performance. Whether you're an Excel beginner or a seasoned pro, learning XLOOKUP should be at the top of your 2025 goals. It’s faster, cleaner, and just makes sense.
Need help converting your old lookup formulas to XLOOKUP? Get in touch—we’re always happy to help.
