Posts

INDEX/MATCH Explained: Why Some People Still Swear By It

-
Image
 INDEX/MATCH Explained: Why Some People Still Swear By It INDEX/MATCH is two separate functions combined into one formula to look up a value — INDEX pulls a result from a range, and MATCH tells it exactly which position to pull from. It does the same basic job as VLOOKUP or XLOOKUP, but it's more flexible about direction and doesn't break as easily when your spreadsheet changes shape. If you've read my post comparing VLOOKUP and XLOOKUP, think of INDEX/MATCH as the formula that came before both of them and, for a lot of long-time Excel users, never really got replaced. I mentioned this one twice in my first post because it kept coming up, so here it is properly. A bit of background on why I still bother with this one. A few years back I inherited a workbook from a colleague who'd left the company, and every single lookup in it was VLOOKUP with a hardcoded column number. Someone had reordered the columns at some point after she left, and nobody noticed the numbers had al...
Post a Comment
Read more

VLOOKUP vs XLOOKUP: Which One Should You Actually Learn?

-
Image
VLOOKUP vs XLOOKUP: Which One Should You Actually Learn? If you're choosing between the two today, learn XLOOKUP first. It does everything VLOOKUP does, fixes VLOOKUP's most annoying limitations, and is now available to most people with a Microsoft 365 licence. That said, VLOOKUP isn't dead, and there are still good reasons to understand it too — I'll get into those. Before I get into the formulas themselves, I want to explain why I'm starting this blog with this particular topic, because it's basically the reason the blog exists at all. I've been working in office jobs for about twenty years now, and in that time I've sat next to, managed, and been managed by an enormous range of Excel users. What's stuck with me isn't the skill gap between beginners and experts — that's obvious and expected. It's the gap between people who use two or three functions their entire career and somehow get by fine, and people who build formulas so elab...
Post a Comment
Read more