4 Reasons why Using of INDEX MATCH is better than using of VLOOKUP

Most of excel users prefer VLOOKUP formula to INDEX MATCH because as they think it’s a simpler. They don’t fully understand the advantages of using of INDEX MATCH formula. Just for info - Excel experts prefer INDEX MATCH to VLOOKUP. And below I will try to explain why.

Reason # 1 – Simpler to choose the necessary column

With the VLOOKUP you specify your entire table array, AND THEN you specify a column reference to indicate which column you want to pull data from.

You should specify entire table (AB6:E22) and then specify a column reference to indicate which column you want to pull data from.

And it can leads to errors when you have a large table and you need to count the number of columns you want. When you use INDEX MATCH, no such counting is required. In INDEX MATCH you directly select which column you want to return.

It’s a small difference, but this additional step leads to more errors. This error is especially prevalent when you have a large table array and need to visually count the number of columns you want to move over.  When you use INDEX MATCH, no such counting is required.

Reason # 2 – No problem when you insert new columns in a table

Any time you work with a large dataset, there’s a chance you’ll need to insert a new column.  With VLOOKUP, any inserted (or deleted) column will change the results of your formulas. The greatest benefit of using INDEX MATCH over VLOOKUP is the fact that, with INDEX MATCH, you can insert columns in your table array without distorting your lookup results.

Take the VLOOKUP example below.  Here, we’ve setup the formula to pull the Population value from our data table.  Because it is a VLOOKUP formula, we have referenced the 4^th column.

If we insert a column in the middle of the table array, the new result is now “1850”; we are no longer pulling the correct value for Population and must change the column reference.

With INDEX MATCH you can insert and delete columns without worrying about updating every associated lookup formula.

Reason # 3 – Right to Left Lookup

With VLOOKUP, because you can only perform a left-to-right lookup, any new lookup key you add must be on the left side of your original table.  And every time you add a new key, you have to shift your entire dataset to the right by one column.  It can also causes the problems with existing formulas and calculations you’ve already created. As you can see from below picture, INDEX MATCH syntax doesn’t care whether lookup column is on the left or right side of your return column.

Reason # 4 – Lower Processing Need

Sometimes it’s required to lookup values for thousands of rows and hundreds of columns. When we added new column (or row) Excel would freeze up and take several minutes to calculate the return values. Replacing VLOOKUP formulas with INDEX MATCH to speed up the calculations.

The reason for this difference is that VLOOKUP requires more processing power from Excel because it needs to evaluate the entire table array you’ve selected.  With INDEX MATCH, Excel only has to consider the lookup column and the return column and Excel can process these formulas much faster.