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