Friday, July 5, 2019

How to using Index Match Formula

Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations).  INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modelling to the next level.

INDEX returns the value of a cell in a table based on the column and row number.

MATCH returns the position of a cell in a row or column.

Here is an example of the INDEX and MATCH formulas combined together.  In this example, we look up and return a person’s height based on their name.  Since name and height are both variables in the formula, we can change both of them!


A               B              C            D             E
                                 1            2             3
                 1           Name     Height     Weight
                 2           Rohit        6.2           185
                 3           Kohli        5.9           170
                 4           Kumble    5.8           175
                 5           Anil          5.5           145
                 6           Sami        6.1           210
                 7           Hardik      6.0           180


Kumble    =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))
Height      Answer is 5.8 

No comments:

Post a Comment

The Great Man and Signature in India - Vol 1

Mohandas Karamchand Gandhi Dr. B. R. Ambedkar   Subhas Chandra Bose Sardar Vallabhbhai Patel Jawaharlal...