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
