INDEX(MATCH()) v. VLOOKUP()

Introduction

In 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.

Comparison

Useful only for Vertically Oriented Data

While true, this ignores that VLOOKUP() has a companion function, i.e. HLOOKUP().

Execution Speed

I won't argue with tests on execution speed.  Point ceded.

"Programming" Speed

As far as I'm concerned it probably takes as long to add a VLOOKUP() as it does an INDEX(MATCH()) formula.  

Reliability

Inserting or deleting a column can foul either Index(Match()) or VLOOKUP() formulas.  That is why they should be bullet-proofed with column Names.

Simplicity

While embedding one function inside another is a level of complexity, complexity of a VLOOKUP() can be somewhat daunting when bullet-proofing

      VLOOKUP(INDIRECT(ADDRESS(ROW(),COLUMN(Prod_COL))),
                     Inventory_Table,
                     COLUMN(IT_ProdRun_COL)-COLUMN(Inventory_Table),
                     False)

Ability to Return a Value to the Left of the Queried Column

Yes, when using VLOOKUP() as it is presented by Microsoft, the user is limited  to returning values to the right of the queried column.  However, it is also possible to look to the right.  It takes some augmentation and has it's own assumptions.  

This is typical data, although not real: A Name, a Date, a Value, not in the order we might like.

So if I want to find the payment and to whom it was made on 9/17/14 I have the options of

• 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.

This third option isn't as outrageous as it may seem. 

• 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())
  =VLOOKUP(SUMIF($D$2:$D$21,$G$3,$A$2:$A$21),$A$2:$D$21,3,FALSE)

Flexibility

Taking the above

• Using a SUMIFS() function (instead of the SUMIF()) allows for multiple criteria selection
  =VLOOKUP(SUMIFS($A$2:$A$21,$B$2:$B$21,Payee,$D$2:$D$21,Pay_Date),$A$2:$C$21,3)
• Making it into an Array formula, now it can return the latest payment to a person
  {=VLOOKUP(SUMIFS($A$2:$A$21,$B$2:$B$21,Payee,$D$2:$D$21,MAX(IF($B$2:$B$21=Payee,$D$2:$D$21))),$A$2:$D$21,3)}

Who uses what

As Columbo used to say, "Oh, one more thing...".  

This was small, but I think important as we select each means to sole a problem.  Just because the "in" group does something doesn't mean it is always the right thing to do.  Not wrong, mind you, just not the right formula for the circumstances.

Newbies v Pros

Sumit 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

Twenty-plus years ago I heard a speaker at the company where I worked talk about the "Priests of IT".  Dan (I forget his last name), started working in the data center (known as the "computer room" when he started) as a high school graduate and was a Director when he gave this speech.  He spoke about how the computer people developed their own language, a language that tended to mark insiders v. outsiders, like Medieval Priests v. the plebeians.  

Sometimes we in IT still gravitate to things that are a bit hard for others to follow.

• 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.

Conclusion

None of this is to say "Don't Use Index(Match())".  To the contrary, I find it useful to know different methods to get things done.  That way when I hit the wall with one then I can try the other.  

Even though I find two things at the hardware store that are both pointy, have heads, and hold things together, a nail needs a hammer and a screw a screwdriver.