I Ranked Every Excel Lookup Function. VLOOKUP Didn't Make the Top 3.

A couple of months ago I posted a tier list ranking every lookup function in Excel.

I figured it would get a few reactions out of the handful of people who care about this stuff. Instead it got loads of comments and the most engagement I've seen in some time.

Which was great, honestly. The rankings took me about four minutes to write. The response that came after has been rattling around in my head ever since.

Let's get into it.

The rankings

Here's where I landed, and I'll stand by all of it.

S tier: FILTER. Doesn't go find you a value. It hands you the whole answer.

A tier: XLOOKUP. Everything VLOOKUP does with none of the footguns.

B tier: INDEX/MATCH. The power move... in 2015.

C tier: VLOOKUP. The friend who's totally reliable right up until you insert one column and it silently returns the wrong number into your board deck. Love him. Wouldn't trust him with my money.

D tier: LOOKUP. It's a museum piece. Next.

F tier: HLOOKUP. It's VLOOKUP doing yoga and nobody is impressed.

Oh, and before anybody brings up speed, let me save you the trouble. If the speed difference between lookup functions actually matters to your file, your file sucks.

The gateway drug

Excel is the Swiss Army knife of analysis, and more to the point it's the one the entire business world already has in its pocket. Finance, accounting, analytics, operations, the folks in the warehouse running their own side spreadsheet. Everybody opens the same green icon.

Lookups are, by most accounts, the first thing anyone learns beyond basic aggregations. It's the first formula that makes the sheet feel powerful, because it's the first time you reach into a data set and pull an answer back out of it.

That moment is bigger than it looks. Excel is a gateway drug to a whole lot of career paths, and it's basically a blueprint for getting under the hood of anything that touches data. You learn to look things up, and before long you're learning how the business is actually wired and you get a glimpse, dare I say, into relational data.

Which is why I care quite a bit about what people learn first.

The knife inside the knife

So here's the actual thesis and why FILTER sits at the top:

If Excel is the Swiss Army knife, FILTER is the Swiss Army knife of the Swiss Army knife. It's as if you flipped open one of the switches and another whole Swiss Army knife came out of it.

The other five are built to give you one answer. Ask them to show you every account in the West region and they'll hand you the first one they find and say nothing at all about the other eleven. Nothing errors. Nothing turns red. They just quietly answer the question in the only way they know how.

FILTER gives you all of them, spilling right down the page.

But wait, there's more! SORT the result. TAKE the top five. SUM it. Stack a header row on top with VSTACK so your columns are labeled. Every one of those is another function you suddenly have a reason to learn, and none of the other five can (or should) be nested like that.

It's a steroid for your Excel muscle and it might be the best rabbit hole in the whole application. It also makes you look like a champ in a meeting, which I'm not going to pretend doesn't count for something.

One word of caution, though: Watch your backwards compatibility. Some of these are newer, and if you're handing files to somebody running an older version you can hand them a screen full of errors instead. That's less of a problem every year, but it's still worth ten seconds of thought before you hit send.

The rules engine

I never cared about this debate in the past because, to each their own, right? Well, let me tell you where this stopped being a preference for me.

I've built a lot of commission models over the years. Not two or three... somewhere around fifteen, real talk.

A commission model is a rules engine whipped up in a spreadsheet. This product pays this person at this rate, but only inside this window, and only after they clear this threshold, and then the accelerator kicks in, and renewals have their own trigger entirely. Every one of those clauses is a lookup with conditions hanging off it.

I built the last one on a single retrieval mechanism using FILTER. Get the structure right once and you can churn custom reports off that same engine all day long, break the links, and hand somebody a clean deliverable, all automated.

A chart of accounts is the same animal. So is anything where you're matching things to other things and the match has conditions on it.

So, if that's the job, then that's what put FILTER at the top of my list.

The broadsword vs the ant

The loudest "alternative" lookup approach I got was Power Query, and it came from people who know exactly what they're doing.

So my two cents: If you can do a thing natively with a function, go do it with the function. I freaking love Power Query, but for something like a basic lookup it's akin to taking a broadsword to ants, and that was a delightful quote from the comments section on my post, and I'm stealing it.

Power Query is genuinely great, but we don't need broadswords when fighting ants.

Why I have an opinion about this

Because reliability, accuracy and resourcefulness were the three things I thought made me a good analyst, and they turned out to be the same three things that made me a decent CFO. Same traits that carried across every desk I sat at, regardless of the weight upon my head.

And once you're the one running the team, this stops being trivia. I wanted the people who worked for me learning the thing that would make them more effective, because their capacity was my capacity. If there's a tool out there that helps more people do better work in less time, I want them using it now, not in three years when somebody finally gets around to showing them.

So why wouldn't I have an opinion?

The punchline

Most people are still using the lookup they got handed at their first job. The tools you reach for without thinking are the ceiling on what anybody can ask you to do.