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()).


         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.
Continue reading

Solving ModelOff Data Analysis problem using Microsoft Access SQL.

MS Access Solution for ModelOff 2013 Data Analysis problem

         Last week we solved ModelOff’s Data Analysis problem from their 2013 championship. Since the second round of 2014 Model Off competition takes place this Saturday, November 8th, let’s pay respect to the data superheroes making it thus far. Our previous ModelOff solution involved using PivotTable feature of Microsoft Excel. Would you believe that we can realistically conceive a solution to the Data analysis problem, using Microsoft Access, or even better, Microsoft’s flavor of the SQL language?

         I am as big of an Excel fan as the next guy, much bigger, actually, on the second thought. However, I also believe that when possible, using the right tools for the job will yield better, faster results, than duct-taping your workarounds. So, why Access? Why on Earth, SQL? Well, let’s go to the source: ” If you often have to view your data in a variety of ways, depending on changing conditions or events, Access might be the better choice for storing and working with your data. Access lets you use Structured Query Language (SQL) queries to quickly retrieve just the rows and columns of data that you want, whether the data is contained in one table or many tables. You can also use expressions in queries to create calculated fields.”
Continue reading