Return Max Value of range that is determined by an Index Match lookup

Question

I need a cell to display the max value of a range who s row is defined by an index and match formula  I know this will be an array function but I m struggling to get the syntax right  Here is what my data looks like  I have it laid out with Column Letters and Row Numbers like Excel   Using the Table Below as reference  in a second table  When I enter b in cell A1 and y in column B1  the formula in cell C1 should return the value 35 because 35 is the maximum value in columns C F on the row determined by A1 and B1 using INDEX and MATCH  Table 1        A      B      C     D     E     F 1    a      x      25    6     23    11 2    a      y      39    15    42    19 3    b      x      28    34    51    24 4    b      y      27    19    15    35 5    b      z      38    26    12    18 6    c      x      12    19    22    15   Now    What I want to do  is to create a formula that finds the max of columns C through F in the row that matches the values in A and B that are given in a separate table  For this example we will write the formula in cell C1  The formula should take the maximum of C through F based on a match of column A   b and column B   y  which the formula tells us is row 4   The value I want in this case would be 35 because it is the max of the 4 columns  C F  on row 4    This is what my second table should look like with the formula being in row C  Table 2        A      B      C 1    b      y      35 2    a      x      25 3    b      z      38 4    c      x      22   I tried this   the formula is in table 2 so it is not explicitly declared in the match portion of the formula  You ll also have to be familiar with tables in excel to get it     INDEX MAX Table1 C  Table1 F   MATCH   A  amp   B  Table1 A  amp Table1 B  0     I then wrap it with Control   Shift    Enter  to Array it   The problem seems to come when I put the MAX function inside the array portion of the INDEX  Are there any ways around this  perhaps I should be using a completely different set of functions

User · Accepted Answer

You don t need an index match formula  You can use this array formula  You have to press CTL   SHIFT   ENTER after you enter the formula    MAX IF  A1 A6 A10   B1 B6 B10  C1 F6     SNAPSHOT

User · Answer

You can easily change the match-type to 1 when you are looking for the greatest value or to -1 when looking for the smallest value

[excel] Return Max Value of range that is determined by an Index & Match lookup

Examples related to excel

Examples related to excel-formula

Examples related to max

Examples related to excel-2010

Examples related to excel-match