IntroductionIn VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here!, Sumit Bansal puts forth an interesting case for using Index(Match()) instead of VLOOKUP().
Like Sumit, as a matter of full disclosure, my bias is towards VLOOKUP(). Why will become obvious as you read this.
Useful only for Vertically Oriented Data
Ability to Return a Value to the Left of the Queried Column
- Rearranging the columns
- Using Index(Match())
- Inserting an Index column on the left side on to the table (say what!) and using VLOOKUP() with a SUMIF() for the first argument.
- Many of our input files are from SQL queries and the results come to us with an Index column.
- Adding an Index column to an existing table is simple, adding the code to continuously refresh it isn't rocket surgery.
- Using something different (a SUMIF function) for first argument seems to have a shorter learning curve than getting owners of the workbooks onboard with Index(Match())
- Using a SUMIFS() function (instead of the SUMIF()) allows for multiple criteria selection
- Making it into an Array formula, now it can return the latest payment to a person
Who uses what
Newbies v ProsSumit wrote, "While you may find Index/Match equally easy when you get a hang of it, a beginner is likely to gravitate towards Vlookup."
First, an Excel beginner is unlikely to touch anything like VLOOKUP(), unless that beginner has been hired specifically for Excel skills and support. Usually VLOOKUP is something people grow into.
I would say most Excel users VLOOKUP() is useful to many semi-casual users of Excel, i.e. those whose jobs are more than 10% working with workbooks, after they have several months to years of experience. Learning VLOOKUP() usually comes after worksheet formatting and SUM() (Excel 101), Sorting and Filtering, SUBTOTAL() and Conditional Formatting (Excel 102),
Second, the first version of Excel I used was Excel on a Mac, before I ever saw a a machine with Windows. Excel was at least the 3rd, if not 4th or 5th, spreadsheet package I had used. I would hardly be considered a "beginner", yet I "gravitate towards Vlookup." One reason is that VLOOKUP(), when properly augmented, like MATCH() augments INDEX(), can be used for multiple argument and left-right returns.
Corollary: Insiders v. Outsiders
- When I hand off a workbook to someone (typically a "semi-casual user") who understands VLOOKUP(), they want to be confident they will be able to read, understand, and fix problems without calling me.
- INDEX(MATCH()) puts them on a learning curve with little benefit to them.