Index-Match Functions in Excel

Today we’ll be learning about one of the very important functions Index function with Match function which is very strong strong combination.

There is always been this debate- which one is better Index-Match or VLOOKUP?

By the end of this tutorial we might be able to find the answer.

Read more about VLOOKUP and MATCH fuction

Lets start, The formula works as follows:

=Index(array,row_num,col_num)
* array is reference
* row_num is row index number
* col_num is column index number
So by providing row and column index it returns the corresponding value.

As we can see the example in the image below

For Book column (F:F) in results table, we are searching from Book column (B:B) and
matching # column (E:E) in results table to # column (A:A) in original table.
here you’ll see column number as 0. Since we have only column we can put 0,1 or nothing.

Now we’ll be comparing Index-Match and VLOOKUP functions

1. Using Index-Match

The formula still working when we inserted few columns because the reference array changes with column insertion.

2. Using VLOOKUP

The results table obtained by VLOOKUP function and is working fine.

Now after adding few columns

The VLOOKUP formula is not working as we want. As you can the see that table array reference changed but the column index remain same which are blank.
that’s why the result is zero.

You may have a better idea now and for me Index-Match is a clear winner here. Comment your opinion.