Jan 22

INDEX – MATCH Formula’s Combination

INDEX – MATCH Formula’s Combination

VLookupSearching a value in the left-most column of table array and return value based on column index number.

 

Combined INDEX MATCH –   formulas work as a right to left lookup, whereas VLOOKUP only works left to right. We can use INDEX MATCH to lookup a value that is to the right of my return value using INDEX MATCH.  This is not possible with the VLOOKUP formula,

 

First, we need understand index and Match formula. We are provided details about how to use index and match formulas and finally we combined.

 

INDEX Formula:

            Return the value from Array based on Row number and column number.                                                                                         

Syntax:                                                                      

            =Index(Array,Row_number,Column_Number)                                                    

                                                            *****Column_Number is optional             

Formula:                                                                   

            =INDEX(A2:A6,3)      

 

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,

                  

Result: 103                                                               

 

Index

 

 

 

MATCH Formula:

            Return a Number based on relative position of lookup value from Array.                                                                                          

Syntax:                                                                      

            =Match(Lookup_Value,Lookup_Array,Match_Type)  

                                                                

Match_Type:

           1 –> Default – return largest or equal match value

           0 –>Return exact match value

          -1 –>Return smallest or equal match value

 

Formula:                                                                   

            =Match(A4,A2:A6,0)

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,

 

Result: 3                   

Index

 

Combining INDEX & MATCH Formulas

 

Index-Return the value from Array based on Row number and column number.      

Match-Return a Number based on relative position of lookup value from Array.

 

=Index(Array,Row_number,Column_Number)

=Match(Lookup_Value,Lookup_Array,Match_Type)

 

When we combine both INDEX and MATCH formulas, the number that the MATCH formula returns becomes the row number for your INDEX formula.            

        

Syntax:          

 

=Index(Array,Match(Lookup_Value,Lookup_Array,Match_Type)                                 

 

Formula:  

 

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,                                                      

            =Index(A2:A6,Match(C5,C2:C6,0)) 

            =index(A2:A6,4)                                                       

Result: 104   

MatchIndex Combined

If it is useful, please provide your comments and like.

Leave a Reply

Your email address will not be published.