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

If you came looking for a battle, you're in the right place.

This is a lookup battle. I'm rating each of the most popular or common lookup functions in Excel, going from F tier all the way up to S tier, and I'm going to share a little bit of the thought process that goes into my ranking.

This is a hot topic in a lot of spheres, and often it's used as clickbait on LinkedIn, and I am no exception. I made a post a few months back sharing my ranking system and it got a lot of reactions. Mostly positive, but a few kerfuffles here and there. Part of the reasoning for it is that what I consider to be the top tier, the S tier lookup, some might not even consider a lookup at all.

Fair warning: one of my rankings changed while I was recording this, and I'll tell you where.

How I'm ranking these

The way that I think about functions in general when it comes to Excel is the inherent utility that the function has for the type of work that I would do as an analyst or in a finance environment.

So when you think about the types of data that you typically interact with, most data are going to be structured in a very common format with columns, where your data are presented as rows with column headers. They may be formatted as data tables proper. They may just be raw data sets. A lot of times your presentation layers are going to be separate tabs that are referencing that and are built off the back of a data set.

And that's kind of the crux of it, because what you're trying to do is extrapolate some sort of value from a data set and use it in the decision making process.

So anything that gets between you and presenting or packaging data to facilitate a discussion is going to be a ding against your rating, effectively.

F tier: LOOKUP

How funny is that? This is a lookup battle, and the worst rated function for looking up, in my opinion, is the lookup function.

You may be looking at this thinking, wait, is that a typo? Should I say XLOOKUP? What is LOOKUP? A lot of people don't even know that this is an actual function. This is an old, ancient function from way back when, and you're going to see some familiar syntax when I start to get into it.

First things first, I type equals lookup and hit tab. So what are we looking at? The lookup value. We're going to look up an ID. Where are we going to find that? In the ID column. And what do we want to retrieve? Revenue. Hit enter, and look at that. It works. Let's try another value. It works.

What could be simpler than that? What are you looking up? Where are you finding it? What are you retrieving? I mean, that's the whole thing, right?

Hold that thought.

So what if, on a wild whim, I decide that I want my account names to be presented alphabetically? Let's do that.

What just happened? LOOKUP is mad at me. It doesn't know what to do, because the column that it's looking up off of is no longer in alphabetical or alphanumeric order. It necessitates that your data are in alphabetical order to work properly, because effectively you're doing an approximate match.

Okay, fine. What if I then just change my reference to the column that is alphabetical? Let's pick something. Harbor Point Systems. That's a real value, right here for 12,060. See, I knew I liked this formula. It works great.

But I kind of want the account IDs to be in order. So I sort them.

Why did my value change? My value just changed to 156,300. That's right here. But that's for Copper Creek Retail.

Oh my gosh, this thing is so confident yet wrong. It's so confidently wrong.

And I don't know that I can actually go deep and explain to you how this function works, other than I can tell you that if it's not presented in the appropriate alphabetical order, it's going to jump to the middle of your data set. So somewhere around here, Lakeshore. And it's going to say, is H before or after L? Well, it's before L. So then it's going to go backwards. Is it before or after I? Well, it's before I. Okay, what about C? Well, it's actually after C. Oh crap, we're broken. Let's just go ahead and retrieve that.

It just gave up. It bailed on us.

So this is not a reliable function in any capacity. I do not recommend it. The utility is just not there. It was probably really cool when it came out, and as long as you knew what you were doing. Not built for the real world these days.

I regret to inform you: F tier. I would have probably rated it lower if I really wanted to. I don't know that we need to talk any more about that. Let's just pretend it never happened.

D tier: HLOOKUP

Here is where I changed my mind on camera.

I published my ranking system, my tiers, on LinkedIn a few months back, and I had HLOOKUP rated on the bottom, mostly because I think it's just really easy to hate. It's like the Nickelback of lookups, right? There are people who actually like it and they understand the utility, and it's easy to pile on and make fun of them.

I don't think that HLOOKUP is actually the worst function. That goes to LOOKUP, as I had it rated. HLOOKUP is just misunderstood, and its utility is very low.

HLOOKUP, if you're not aware, is horizontal lookup. It's designed in tandem with VLOOKUP, for vertical lookup, for data that are presented where your data is inserted on a horizontal plane. So your headers are listed as a column, and then each subsequent data set is added horizontally. Which is why applying it to a data set like this feels a little clunky, but we can make it work.

The way that V or H lookup works is it starts with what is the first row or column. In HLOOKUP it's the header column or row, first value. Then I need to find it. And then how many rows down do I need to go?

It worked. We made it work. But you might have noticed this is not connected to the cell above it at all. So if I change that, it does nothing. What we would need to do is make it not be static, and instead dynamic, and we know how to do that via the MATCH function.

So now we have to finagle this thing if we want to make it a little bit dynamic. That worked. It's just not ideal.

And then what happens if we have a value that's not in the table? We got a gotcha. Oh golly. It just throws up its hands and doesn't know what to do with itself.

If we wanted a fair fight, we could present the data the way that it would ideally work, in which case a horizontal lookup makes more sense. And that's natively how HLOOKUP would work, because that's the layout of the data.

All this to say, HLOOKUP I believe is not F tier, but it's D tier. It's not super useful because of the orientation the data traditionally should be in. It requires a lot of finagling, and if it can't find the value, it just throws its hands up.

C tier: VLOOKUP

Onward to C tier, where our friend VLOOKUP exists.

VLOOKUP was all the rage, and for a lot of people it still is all the rage. I don't want to throw shade. If it works, it works. If it works for you, then fine.

But here's the thing. It has flaws.

What am I looking up this time? ACC-1001, because it is in the first column of our data. Set the array, and what I want to index off of is going to be column five. And we want an exact lookup. So, 482. That should look familiar, because it's the first item. I want to change this to anything, it will update accordingly.

So here it works. But where it doesn't work is if I insert a column. Look. Now it's pulling a zero. It's pulling the zero because now the fifth column is over here.

So if you want to protect against that, once again you need to throw in a MATCH to make the reference be dynamic. Now it'll work. Now it's a little dynamic. So you have to make adjustments.

It's going to break the same way the HLOOKUP breaks on the gotcha. It doesn't know what to do with it. Throws up its hands. No error handling. It's only going to bring back one value. You can do approximate or exact match.

But in general, it's kind of a workhorse. It's dated, because there are better options.

B tier: INDEX/MATCH

So let's get into one of the better options.

This one was probably the first intimidating function, or combination of functions, that I encountered in the wild as I was coming up the ranks.

And it's funny, because we're giving VLOOKUP and HLOOKUP crap because you need to add a MATCH to make them work. Yet INDEX MATCH is inherently two functions coming together. It's just, for some reason, more generally acceptable.

I think the reason INDEX MATCH wins is because it can go horizontal for as long as it can go vertical. Wherever your index goes, it can go. Not only that, and I'm not going to get into it here, but you can do INDEX MATCH MATCH, because your index is actually looking for rows and columns.

So, INDEX. Kind of think back to the lookup function. What are we retrieving this time? Where are we going to find our value? It's going to be here in the revenue column. What are we retrieving? Well, we want to match where this value shows up in this column, and we want it to be an exact match. Close both parentheses. And that works.

If we had not done a MATCH, the way that this works is if I want to pull the ACC-1001 value out of this, I need to know that it is the first value. So I just need to go row number one. All we're doing is throwing in a MATCH function to overwrite that. Similar principle to what we just talked about in the VLOOKUP and HLOOKUP. It's just inherent.

And now you can do this more flexibly. I can bring back the account name very easily. Boom. Done. Easy. I don't have to go count columns or do any of that stuff. It's just all in your reference.

So INDEX MATCH wins on flexibility. It can go horizontal, it can go vertical, and it's pretty quick at processing.

What happens when you have a value that isn't there? Enter gotcha. It breaks. So for error handling, you have to wrap this thing in an IFERROR. That's an important thing to note. And it only goes top to bottom. That's another thing to note.

A tier: XLOOKUP

XLOOKUP. I don't want to say it retired INDEX MATCH, but it kind of retired INDEX MATCH for a lot of power users. For some of us it took a little bit of time before we got into it, because man, INDEX MATCH just works. When you know how to use it, you whip it out and it's old reliable. It does its thing.

XLOOKUP, though, has a few bells and whistles.

What am I looking up? I'm looking this up. I'm going to look it up from right over here. And what do I want to return? This guy.

Syntax wise, that just makes a heck of a lot of sense. Same as the old LOOKUP did, right? INDEX MATCH is kind of confusing because it's backwards. You're first saying, where's the value I want? And then you're going back and finding where it's going to be via the MATCH.

So here, that's all you need. And you hit enter and you're there. It's every bit as flexible as INDEX MATCH was. So if I wanted to instead retrieve the account name, I could do that, no problem.

If something comes up as a gotcha, it's going to break. But you can tell it what to do instead. If not found, no match. That's kind of handy, right? So you don't have a bunch of errors blown. And you could even put a code in there so that you know what to look for in instances where you don't have a match.

Also, let's look at some of the other bells and whistles. Match mode: you can say exact match, next smaller item, next larger item, wildcard character match, regex match. Search mode: first or last, binary. All sorts of fun stuff. This thing is actually pretty well thought out.

So why isn't it at the top?

Because it gives you one result.

What happens if instead I'm looking up region, and I want the South region? It's going to give me a value. It's going to give me the first value that it comes across. Or if I told it to go in reverse order, or in some other order, it will pull that thing. But it's never going to give me more than that one thing.

That is where FILTER comes in.

S tier: FILTER

That is why FILTER is S tier, because it can do all the things.

First off, let's just prove to you that this thing can do a basic lookup. What are we trying to pull back? Well, our universe is here. It's kind of similar to INDEX MATCH. What are we going to include? I want to include where the account ID matches. Enter. And it found it. If we change it, it did it again. What happens if it's a gotcha? It has a calc error, so we do need to accommodate that.

But here's the big but.

What if instead I want to see region South, and I want to see the revenue? It's going to bring back all the results. It's going to bring me back the answer. It's going to bring me back something that I can work with.

I'm going to grab headers here. You could VSTACK this if you want. And it just gave me the values how I want. Now if I want to change this to West, there we go. And this could be on a different tab. This could be on a presentation layer.

Now you can choose how you want this to present. Now you can choose if you want to sort this a certain way. Now you can choose if you want only unique values, and you want to sum it. You start to see where we're going with this.

The light bulb

When I first saw the FILTER function, I thought, that's the dumbest thing I've ever seen.

In my post I talk about not buying into the hype, and it was because I was very set in my ways of doing a lot of things.

Over time, I ran into situations where I would be constructing a view, and I'll be very explicit: commission statement modeling. Building custom commission statements for teams. You've got big data sets coming out of your CRM, and you're running calculations off of that, and then you're presenting all the different views for all the different salespeople. Once you build the template and it works, then you can just repeat that process and spin those out.

Once I started using the FILTER function to build those views, it was like a light bulb went off, because I realized, oh my gosh, I've been sleeping on this thing.

The flexibility with which I can basically set what inputs I want. I want this salesperson and I want this customer, or I want this date range for this thing, and it will build those reports and they will be beautiful. You can sort them. You can grab unique values. You can wrap the whole thing in a sum. You can choose which columns you want to present. You can change the order of the columns you want to present. It plays nice in the sandbox with all these other spill and dynamic array functions.

But can't I just use a filter button?

I'll go back to my original refutation of this thing. You go up to the ribbon, add filters, oh gee golly, filter. I'm just going to go ahead and filter to South. What, I'm not capable of doing that on my own? There's my results, I've got everything, cool.

But you just completely compress everything. If I were to take this, convert it to a table, add slicers, yeah, maybe I can have buttons. But again, you're playing with a gigantic data set. This thing is super fast, super slick. And you don't have to have somebody interacting with a bunch of switches on a big data set if that's not what they need.

This is not something that a quick pivot table can handle. This is something that's very slick, a very easy interface.

And the value of putting something like this together is that you can have summary level, and then the more detailed level, all of it extrapolations of your core data set. Immensely valuable to kicking off and having sustained conversations where you can change things on the fly.

Your requirements are only limited by your imagination.

That is why the FILTER function, in my opinion, is the king of lookups.

So there you have it

That is my tier rating system of the lookup functions in Excel.

If you want to fight about it, go ahead and drop your harsh words for me in the comments and let me know what you think. I'd love to hear it.