INDEX and MATCH Function Dynamic Formula

* INDEX and MATCH Function :-

 We have covered the basics of INDEX and MATCH, how can we combine the two functions in a single formula? Contemplate the data below, a table showing a list of  product name and amount 

Syntax :- =INDEX(Array,match(lookup_value,lookup_array,{March type},0/1) Enter

Example :- =INDEX(H5:H11,MATCH(K5,G5:G11,0)) Enter

  

 * It is two-way lookup with INDEX and MATCH.

Below we used the MATCH function to find the row number for any given sales person. To keep things, Product we hard coded the column number 2 to get sales for product name. How can we make the formula fully dynamic, so we can return sales for any given sales person in any given Product Name? The trick is to use MATCH twice – once to get a row position, and once to get a column position.

Note :-we can call Dynamic formula of INDEX AND MATCH

Example :-=INDEX(H5:I11,MATCH(M4,G5:G11,0),2) Enter