VLOOKUP() vs. INDEX(MATCH()), which should you use?!
I recently read an excellent article, where its Excel MVP-author makes a very compelling, albeit biased argument towards using INDEX(MATCH()) function combo, instead of taking the VLOOKUP() route. That article jokingly proclaimed the end of the VLOOKUP vs. INDEX(MATCH()) debate. Interestingly enough, some of the heavy-weight Excel MVPs and expert users weighed in on this issue, in essence continuing the debate. One of the commentators, published an equally brilliant rebuttal, skewing the victory towards the VLOOKUP function. Being a biased VLOOKUP proponent myself, I concur with his conclusion completely. In addition, I’d like to throw in a crazy idea of using SQL as a better, faster, more flexible, and easier solution, comparing to either: VLOOKUP(), or INDEX(MATCH()).
Popularity
I believe there is no disagreement here: VLOOKUP enjoys tremendous popularity. People have different opinions whether this popularity is justified. I belong to the “ease of use” and “ease of learning” VLOOKUP camp: two of many factors that help us explain high adoption rate of this function among all Excel users.
Ease of use
It’s hard to argue with the ease of use, when it comes to applying VLOOKUP() function. It has a very straight-forward syntax, and calls for 3 required and 1 optional arguments. Contrasting this with having to use 2 different functions and multiple arguments with the INDEX(MATCH()) function combo, and VLOOKUP emerges as a clear winner in this category.
Ease of learning
To demonstrate the point of being easy to learn, one can simply follow a very creative VLOOKUP tutorial . Using Starbuck’s menu as our data source, one can easily explain the concept of a VLOOKUP function to any beginner Excel user. I would imagine, that it can also be explained to any non-Excel user as well. On another hand, explaining the concept of INDEX(MATCH()) to a beginner Excel user is a rather daunting task. In addition, you simply cannot explain it to anyone who has never worked with a spreadsheet application before. As organizations become more data-centric, they require so-called “advanced” Excel skills of its knowledge workers. Since VLOOKUP is one of the most popular search terms for Finance-related job listings, AND its very easy to teach; we have a win-win situation, enabling us to effortlessly improve everyone’s earning potential.
Flexibility
This is is the point that keeps this debate going. Surely, using the out-of-the box VLOOKUP function, it lacks some flexibility associated with the INDEX(MATCH()) function combination. Specifically, the users are restricted to looking up values to the RIGHT of the look-up column. If one cannot reorder their columns, they can easily create a helper column(referencing values from the source column), hide it and use it in the VLOOKUP function. This concept does not escape the most novice users. A more involved solution would be to use some formula workarounds (Think: SUMIF() or CHOOSE() functions as your VLOOKUP arguments), that would be as difficult to follow/learn as the INDEX(MATCH()) route. For the sake of this argument, let’s mark the INDEX(MATCH()) as the winner in this category.
Speed
It looks like there is some empirical, yet conflicting evidence supporting either of the solutions to be faster than the other. Please see links below. In addition, there are some VLOOKUP workarounds that improve its performance. Nonetheless, let’s give the point to the INDEX(MATCH()) combination, since it should outperform repeat use of multiple VLOOKUP statements, while looking up content from multiple columns.
There is no shortage of articles written on this topic by most prominent Excel thought leaders. Below is a small sample of links also found in the comments section of the referenced article:
http://mrexcel.com/articles/excel-vlookup-index-match.php
http://exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html
http://exceluser.com/formulas/excels-vlookup-vs-index-match-functions.htm
http://www.excelhero.com/blog/2011/03/the-imposing-index.html
http://www.decisionmodels.com/optspeede.htm
Conclusion
My, somewhat biased conclusion is leaning towards the VLOOKUP function. It’s easy to teach VLOOKUP to any Excel novice. It’s simple to program and utilize. There are some easy workarounds fixing its built-in limitations. Assuming that the performance speed is not an issue, any advanced Excel user can use either VLOOKUP() or INDEX(MATCH()) and accomplish the same results. At that point it becomes a matter of preference and personal choice; after all, there are different ways to accomplish the same task in Excel… Since few matters are really black and white, your decision to use either methodology might depend on your unique needs. If speed is in fact an issue, one would assign a heavier weight to this factor and would use the INDEX(MATCH()) approach. On another hand, if you have to educate 100 or so users of your reporting on how to use INDEX(MATCH()) functionality, and it takes them 30 minutes to learn this concept, you are investing 50 worker hours in this effort, which might not pay off in the future.
The above conclusion is based on hypothesis that having to choose the lesser of two evils, I would choose the VLOOKUP() function. The truth of the matter is, if someone has any concerns regarding performance of either of these methodologies, most likely they are using the wrong tool for the job. Looking up values from different fields of the same or different database tables is the function of SQL equi-joins. Most of our data is retrieved from corporate databases anyways. Instead of having to manipulate our data dumps after the fact, we have to either work with our IT partners to craft query design to our specifications, or, if possible, compose those queries ourselves. Writing a SQL statement to the tune of SELECT FieldA, FieldB from tblC or even SELECT tblA.FieldA, tblB.FieldB FROM tblA, tblB WHERE tblA.FieldC = tblB.FieldC would score us a point in all categories: Ease of use, Ease of learning, Flexibility, and Speed. Unfortunately it’s as “popular” way of accomplishing this task, as using the INDEX(MATCH()) approach, perhaps even less popular.
As Chase Jarvis famously quipped: “The Best Camera Is The One That’s With You”, many of us are forced to use the tools that we have at our disposal: Excel, instead of the tools that we should be using: corporate database. This situation will not improve until our IT colleagues stop viewing us as outsiders, but instead enable us to achieve organizational goals.
Original photo credit: Flickr
