Why Excel Vlookup Sucks (And What To Use Instead)

I have used Microsoft Excel for my entire career. Whenever I open a spreadsheet and see someone using vlookup() to look up a value, I feel sad for that person. It’s like I’m watching someone trying to light a fire by banging rocks together, and I have matches in my pocket.

You, two minutes from now

We use vlookup to lookup a value in one column, based on matching a value in another column.

Imagine you want to look up a phone number in the phone book (am I dating myself…?). You would look up the name of the person you want, then call the corresponding phone number.

So why does vlookup suck for this purpose?

#1 The column you’re matching on must be the first column in your selected range. If the values you want are to pull are to the LEFT of what you’re matching on, too bad. Real data sets are not always like phone books.

Uh-oh

#2 The lookup column must be in the same range as the match column. If it’s elsewhere, or even on a different tab, too bad.

Uh-oh

#3 You’re required to input the column number you want to lookup, based on the match in the first column. What’s really dumb is that the match column counts as a column. When would you ever lookup a value in the match column — you already have that value! It’d be like going to the phone book and looking up “John Smith” in order to find “John Smith”.

Phone number SHOULD be lookup column #1, not #2

#4 It cannot look up values across columns, only rows. For that you need hlookup().

Uh-oh

Even Microsoft has summarized the shortcomings of vlookup in a nice image:

Ok ok enough bashing!

I’m telling you only because, why put up with that when there’s a much better alternative?

Introducing…index match!

Here’s what this bad-ass formula looks like:

=INDEX(B1:B10,MATCH(“Bob”,A1:A10,0),0)

It is equivalent to:

=VLOOKUP(“Bob”,A1:B10,2,FALSE)

Let’s break it down:

INDEX() looks up a value in a range, at the intersection of a given row and column.

=INDEX(range, row #, column #)

So in the phone number example above, I give it the range of phone numbers, located in B1:B10, and I’m about to tell it which row number with…

MATCH(), which looks for a value in a given range, and returns the position.

So if Bob was in the 3rd row in the range of names A1:A10, MATCH() would return 3, and INDEX would then pull the value from the 3rd row in B1:B10.

=INDEX(B1:B10,MATCH(“Bob”,A1:A10,0),0)

=INDEX(B1:B10,3,0)

=[The Third Row of B1:B10]

=[Bob’s Phone Number]

Boom

Why is this so awesome?

1. Your match range and lookup range can be completely different, even in different tabs if you like.
2. You don’t have to define which column you want to pull from — the lookup range is self evident, it’s only one column wide

So what about horizontal lookups?

=INDEX(A2:D2,0,MATCH(“Bob”,A1:D1,0))

Boom. Now instead of telling index which row you want to look up, you’re just telling it a column number instead.

OK fine, you can’t quite make the conceptual leap from vlookup, and you still want to have a defined range where your match and lookup are in the same table.

INDEX(B1:C10,MATCH(“Bob”,A1:A10,0),1)

No sweat. Just tell it what column number to use (and unlike vlookup, you don’t need to include the match column in your index range, so column number actually makes sense).

Ok, last time I’m using this image, promise. But isn’t index(match()) cool??

You’ve now hit the magic moment. Go forth, use it, and share this article with any rock-banging coworkers.