dimanche 22 février 2015

Excel formula - Max solution several criteria

Link to visual:

http://ift.tt/1EEuIS9


The logic behind: First i want it to maximize the column matching to cell B7 (one type A, one type B).


But i want to insert another condition: The column matching cell B15, must be above the value of D15 (one type A, one type B).


this results in the ones showed in A19:C20.


Progress:

This is my formula in cell C11, which leaves med with the results without the extra conditions:



=INDEX(Table1[Name];MATCH(1;IF(Table1[Type]=B11;
IF(INDEX(Table1[[X]:[Y]];;MATCH(B$7;Table1[[#Headers];[X]:[Y]];0))=MAX(IF(Table1[Type]=B11;
INDEX(Table1[[X]:[Y]];;MATCH(B$7;Table1[[#Headers];[X]:[Y]];0))));1));0))

Aucun commentaire:

Enregistrer un commentaire