I Use VLOOKUP Far More Often than Index and Match

March 10, 2018 - by Bill Jelen

I conceptually know in my head that INDEX and MATCH are superior to VLOOKUP. But to this day, I will confess that I use VLOOKUP 99% of the time. Here's why.

I am trying to build spreadsheets that other people can understand. When I do my live Power Excel Seminars, a typical audience of 100 people will have this profile:

• 15 will not be familiar with VLOOKUP or will ask if I can explain VLOOKUP very slowly
• 83 will be comfortable with VLOOKUP. (I have very smart audiences. They use Excel 40 hours a week. And those are the weeks they are on vacation.)
• 2 will be INDEX and MATCH proponents.

With only 2% of the people familiar with INDEX and MATCH, far less people will understand my formulas if I use INDEX and MATCH. Plus, VLOOKUP will handle my situation 99% of the time. Here are the times when I abandon VLOOKUP

INDEX and MATCH is better at handling a two-way lookup.

Note that the example above is simpler if you use a VLOOUP and then a MATCH for the third argument like this:

By the way, when the INDEX/MATCH proponents tell me that VLOOKUP can't easily look to the left of the key as in the following example, I reject that argument. Any rational person would copy E2:E15 over to H2:H15 and do a VLOOKUP.

But here is the one case where I will use INDEX and MATCH. Below, I have to do 12 columns of VLOOKUP. Check out that awesome formula with one absolute reference, one reference freezing the row and one freezing the column. While that formula is fancy and cool, it is inefficient. Whatever time it takes Excel to find Date when looking up B4, it will take the exact same time when it looks up Date in C4:M4.

The altenative: insert a hidden MATCH column in B and then use 12 columns of INDEX. This solutions is 11 times faster than 12 columns of VLOOKUP. The MATCH formula shown in B1 is used in B4:B9999. The INDEX function shown in C2 is used in C4:N9999. Another advantage: you don't need the helper numbers in row 1 anymore.

I am happy to co-exist with those who reject VLOOKUP and use INDEX & MATCH 100% of the time. But I ask the same from the INDEX/MATCH people: I have good reasons for continuing to use VLOOKUP.

Every Saturday, an admission of guilt. I will reveal my bad habits in Excel and discuss why you should do what I say instead of do what I do.

Title Photo: Piotr54 / Pixabay