Why You Should Never Use VLOOKUP Again for Social Analysis
One of the things I love about Excel is that there is very rarely only one way to do something. Like any programmatic language, there are different schools of thought about how to address your problem set and get results.
The Quick Guide to Social Media Attribution Models
Sometimes it’s okay to throw together something hacky and get quick results if you know you’re doing one-off analysis, and sometimes you’re tasked with building a dashboard that needs to be used over and over in many different scenarios. Some people want to do everything in pivot tables, and some people like the alchemy of formulaic solutions. That’s the magic of Excel land: much like Alice’s trip down the rabbit hole, getting to where you want to go is a matter of which potion you want to drink and which caterpillars you choose to trust on your journey.
INDEX/MATCH vs. VLOOKUP
That said, there is a point on which I refuse to relent, refuse to see the other side. Like a diehard European soccer fan, I’ve picked a camp on one issue and am more than happy to sing boisterously and wave flares for my side.
That side is INDEX/MATCH, the arch-rival of cross-town club VLOOKUP. Sure, VLOOKUP is popular. Everyone knows VLOOKUP. I’ll even admit I used to be a fan, telling everyone who asked — no one, by the way — how much I knew about Excel because I knew how to vertical lookup.
But those days are gone, and I’ve seen the light.
VLOOKUP is an admittedly very helpful Excel formula that helps correlate data in big spreadsheets. Imagine you have a big table of Tweets with engagement data, and you want to see how many Retweets that one really creative Tweet you tweeted last month received. VLOOKUP would be an awesome way to get this number without having to scan your hypothetically enormous table for the exact row. With VLOOKUP, you can throw a unique identifier into the formula, and the answer will pop out for you.
INDEX/MATCH is the pro version of VLOOKUP. It’s better in almost every single way, and in this here manifesto, I’m going to explain why you should stop using VLOOKUP and switch to INDEX/MATCH.
Before we begin, let me clarify that, unlike VLOOKUP, INDEX/MATCH is actually two different Excel functions used in tandem to achieve the same results.
VLOOKUP Formula: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
INDEX/MATCH Formula: INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])
While confusing at first, it’s part of what gives the dynamic duo their strength, but we’ll get to that. First, let’s cover the basics of what makes INDEX/MATCH so great.
VLOOKUP Makes You Count Columns
If we take a look at the two signatures of these formulas, you can see that VLOOKUP has a number that represents the number of columns over from your reference column you want to snag data from.
This is my first point, because it’s the worst offender, and a big reason VLOOKUP ends up being so problematic. Now, you might have your Tweet ID’s in Column A and your Retweets in Column D — but what if you want to change that? You’re going to have to change that little column offset number EVERYWHERE. You’ll have to hunt each and every one down and augment them by hand. If that doesn’t make you cringe, you’re a stronger soul than I.
Even worse, what if our table changes and the Twitter ID is now on the right of the Retweet column? You’re in even worse luck, because now you need to use a whole other formula aptly called VLOOKUPNEG.
If this sounds terrible to you, it’s because you have good intuition and are likely coming to accept the glory of INDEX/MATCH in your heart and soul.
You don’t need any of these shenanigans with INDEX/MATCH, because with INDEX/MATCH, you refer to the column you want to look up from with an actual column reference, like “D:D.” Need to insert a column? Your INDEX/MATCH will update auto-magically to reference the new column where your expected data resides. Need to switch the left-right orientation? Much like a honey badger, INDEX/MATCH don’t care. It needs column references, not ordinal numbers in an array reference, so it doesn’t care how far away a given column is (compared to VLOOKUP, where you define it based on how far away it is).
VLOOKUP Limits Lookup Options LIKE A HUGE JERK
Let’s nerd out some more and look at that VLOOKUP formula signature again. We’ve got this optional argument at the end, which is a TRUE or FALSE value depending on whether we want an exact lookup match (the former), or a sort-of kind-of passive-aggressive match, where we just get something kind of close to what we specified to lookup.
This actually sounds kind of nice, right? Sometimes you really are just looking for something close. This is a legitimate use case. Well done, VLOOKUP.
Oh, no, wait, you lose again, VLOOKUP, because INDEX/MATCH gives you a whopping 50% increase in lookup options! Look at that value, am I right? Specifically, MATCH has three possible lookup options available: -1 (meaning give me the smallest value that is greater than my lookup value), 0 (meaning I want the EXACT match), and 1 (meaning the largest value that is greater than my lookup value). Especially with something like dates and grabbing the first value prior/post your lookup date, this is an AWESOME tool to have.
Are you starting to see the value yet?
VLOOKUP Is Strict in its Use Case
Finally, I want to point out how inflexible VLOOKUP is compared to INDEX/MATCH.
VLOOKUP is totally suitable for doing lookups with a defined table of defined dimensions and defined columns. But how many of us work in a “defined” world? In social media, the only constant is change. One day your API returns a particular metric, the next day it’s gone with a little blog post from the platform about its deprecation to try to soothe your soul.
INDEX/MATCH is prepared for this chaotic world we call social media. There is so much you can do with this formula if you use just a bit of creativity to make your life a million times easier.
When I talked about how it was a good thing that INDEX/MATCH is two formulas, this is what I was getting at.
The best example of this is the simple fact that INDEX/MATCH works equally well vertically as it does horizontally. That means that, unlike VLOOKUP, you can use it to position itself in a row, and then lookup up or down, rather than just position itself in a column and then lookup left and right.
Having two rather agnostic formulas in INDEX and MATCH is significantly better than one formula that is rather adamant in its use case. There are many more ways you can tweak each formula to make it work for you, or compose it creatively inside another bigger function for a specific task.
If you’re not, at least I tried — but hopefully you’ve come to rejoice in the name of INDEX/MATCH. Sure, it’s not perfect, but when we break it down and pit it head-to-head against VLOOKUP, INDEX/MATCH comes out on top each and every time. It’s my absolute most-used Excel formula, and I’m a proud disciple. Hopefully it can help you out in your own social media analytics journey.