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]&[@B],Table1[A]&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?
