INDEX MATCH Formula in Excel with Step by step illustrations

INDEX MATCH FORMULA

This function is very useful for that kind of job in which we have to find the things from one table and put in front of values present in other table.

Summarising the complete Post

1.              Write Index formula

then

2.     Replace the Row_no part of INDEX formula with MATCH formula.

Comparison of two Formulas

1. Index match 
2. Vlookup 

Index match and Vlookup both are similar. Using both in practical examples it is observed that  index match is better than Vlookup  formula.

          Because;

1. In the Index match formula lookup value can be from any column whether they are in right side or the left side of column containing our required value.
2. It requires only two columns :

                   a). Column in which Lookup value is present.

                   b). Column from which Resultant value is taken.

 However

1. Vlookup   formula works only from left side to right side of table.
2. It requires a continuous array of table and if table contains many column or some column are hidden then it becomes very difficult to calculate index_no for our formula. 

First method (Index Match formula) 

Let we want that when we write “NP”, The formula give us value “7875” As shown in the following fig.

It can be done by using two formulas

          1.     Index
                   2.       Match

1.    INDEX

Syntax
=INDEX(array, row_num, [column_num])     

1.    array :Range from which we have to select our value.(in our example 3rd column i.e “C5:C12” (No. of employees) is the array because we need 7875 from it.)

2.      row_num : Row Number in which 7875 is present( In above example  NP is on 3rd  row so we write 3 in index formula.)

=INDEX(C5:C12,3)

           It will give us 7875

Important thing in the above formula is 3 i.e 3 is Input for Index formula.

2.    MATCH

Syntax

MATCH(lookup_value, lookup_array, [match_type])

1) lookup_value: The value that we want to match in lookup_array. For example, when we look up someone's number in a telephone book, we are using the person's name as the lookup value, but the telephone number is the value we want. (In the given table abov, lookup value is NP.   
 {lookup_value in Vlookup and indexMatch formulas has same nature.}

2) lookup_array: The range of cells being searched. In the given table above, lookup array is A5:A12

3) [match_type]:zero “0” for exact match. It is recommended.

                

a)    =MATCH("NP",A5:A12,0)  or;
b)   =MATCH(F7,A5:A12,0)

(We have used the reference of the cell in second formula. F7 is the cell reference where NP is written.)

Both (a,b) will give us 3

Summary of both formulas

INDEX MATCH

INDEX(C5:C12,3)

MATCH(F7,A5:A12,0)

Now we will copy the complete match formula and paste in place of 3 in the Index formula.           

It gives us following string.

=INDEX(C5:C12,MATCH(F7,A5:A12,0)

                                        

As discussed earlier important thing in the Index formula is 3. Instead of writing the 3 we have used Match formula in its place this formula gives output 3 , which is used as Input for Index formula.

Summarising the complete Post

1.              Write Index formula

then

2.     Replace the Row_no part of INDEX formula with MATCH formula.