Haters gonna hate (Excel Vlookup formula..)

One of the oldest dilemmas of life is "which came first: the chicken or the egg?" and still nowadays it is not easy giving an objective answer to this philosophical question. Then there is the other great dilemma of all times, the one usually returning about 1.050.000 results in 0.52 seconds from your Google search: VLOOKUP vs INDEX MATCH. While this debate has apparently the latter formula combination as a clear winner nonetheless, this argument can get surprisingly heated, sometimes too much.

Let's have a quick glance at what the aforementioned formulas can do:

VLOOKUP

VLOOKUP allows the user to retrieve data from a specific column of a table/array. The formula syntax has four arguments and it is straightforward:

1. the lookup value
2. the range containing the lookup value
3. and the column number in the range where I actually want to lookup
4. the fourth argument is optional, 9 out of 10 times it will need FALSE for an exact match, however, be careful leaving it blank, the default option is TRUE, that is an approximate match.

Let's see an easy example: here I'm looking for the postal codes of different addresses in the Blu table, using the Orange table as the source. Therefore, the first argument will be the address in cell C6, the second argument is going to be the cell range that contains the data (i.e. the Orange table between cells H6 and I13, for practical purposes I set them as an absolute reference pressing the F4 key). The third argument is the column index number, in layman's terms the table column where I'm searching and finding my result, in this case, column 2. As mentioned above, in the last argument I typed FALSE for an exact match (inputting TRUE would make Excel assume I'm looking for an approximate match and that my table is appropriately sorted).

The biggest limitation of VLOOKUP is that it can only look to the right side of the table to retrieve data. Some other limits of this formula are: it always finds the first match (so if there are duplicated values it could show a wrong outcome), it is not case-sensitive, if one or more columns were added to the lookup table it would break the formula because the column index number might be hard-coded (as in my above example).

INDEX MATCH

INDEX MATCH is a combination of two formulas, that makes the syntax slightly more complex. In a nutshell, the MATCH function searches for a specific item in a range of cells and the outcome is the position of that item in that range. The INDEX function returns a value in an array/table based on the specified row number (and/or column). When combined, MATCH provides the row number to INDEX and combined they can provide the actual text result.

Easier to do than to say this time: another easy example looking for the postal code, but this time I have put the address column in the lookup table to the left side on purpose to show that INDEX MATCH can go in either direction.

If the formula had to be written in plain English it would be read like the following:

= INDEX(Column I want a return value from, MATCH(the Lookup Value, Column I want to Lookup against, Match_type is usually 0))

In the above example, I look at column I to find the value in cell C6, the MATCH formula result is 4, meaning that the fourth entry in the range is the one I was looking for. Excel then looks into the column H range and counts to 4, where the result is.

Why the combo INDEX MATCH wins?

1. Because you can indifferently "lookup" to the left and to the right,
2. because, apparently, it is faster and less heavy for Excel compared to VLOOKUP
3. and because, most of all, you can add or delete columns and this won't affect the formula. In my personal experience, it is very user-friendly with Excel Tables (Ctrl + T), because the formula arguments will have the column names and they will be really easy to read and understand.

The outsider: power Excel...

There's actually a third way hiding in plain sight: that is Get&Transform and PowerPivot, using respectively OUTER JOINS in the former Power Query and RELATE (even though creating a relationship between two tables should be more than enough) in PowerPivot/PowerBI. But this article is about VLOOKUP vs INDEX MATCH, so no outsiders for now.

Life is too short to waste time hating anyone (and any formulas too).

I'm being honest, as many people writing and reading about this topic I have had my moments suggesting everyone around me it is totally better to use INDEX MATCH compared to VLOOKUP [and I'm sure some of my readers will be laughing out loud right now thinking of me being the INDEX MATCH crusader], but when I say I'm grateful to VLOOKUP I mean it from the bottom of my heart.

Why? I have used VLOOKUP to create several important documents when I started working in Finance&Accounting, to be honest even before when making the best of this formula creating spreadsheets for fantasy football private leagues (before the internet did it quickly, of course). The moment I got the grasp of VLOOKUP gave me a great boost to learn and improve my Excel skills. It has all the features anyone needs to move from beginner to intermediate level: values, array tables, boolean logic.

The INDEX MATCH combination is absolutely more flexible, it can do things VLOOKUP can't, like looking up to the left of a table for example, but it may be more complex to learn and know inside out at the beginning, especially moving to multiple dimensions like INDEX MATCH MATCH. As far as I can say, I managed to master all of the above combinations while improving my Tables (Ctrl + T) skills, so that helped giving more meaning to the different function arguments.

Simply put, there is no contest, no dilemma: you can like both, you can like neither and use VBA, you can love either the former or the latter, they're most of all two tools in our toolbox: use them well, use them wisely, use them at their best!

How about you? Ever been against VLOOKUP? Any comments? Feel free to add your thoughts here below!

Disclaimer: I'm not affiliated with Microsoft in any way. Opinions are my own and not the views of anyone else. No spreadsheet has been harmed in the writing of this article.

Legal disclaimer: dear Taylor Swift, I know you have had a long history of sending lawyers after anyone, I swear I meant no offense quoting your lyrics and publishing your free image from the internet, I have the utmost respect and consideration for your work, wishing you and your legal team a wonderful day!

Peace, love, music... and VLOOKUP!