7

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
Ashton Sheets
  • 513
  • 6
  • 13
  • 21

2 Answers2

13

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

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 5
    You can use INDEX and MATCH if you want. This one is longer than Sid's suggestion but doesn't need CTRL+SHIFT+ENTER....`=MAX(INDEX(C1:F6,MATCH(1,INDEX((A1:A6=A10)*(B1:B6=B10),0),0),0))` – barry houdini Aug 15 '12 at 22:21
  • 1
    @barryhoudini - `=MAX(IF(MMULT((A1:A6=A10)*(B1:B6=B10),1),C1:F6))` also works. `MMULT(...,1)` seems to work more generally than `INDEX(...,0)` for making formulas non-array entry. – lori_m Aug 20 '12 at 14:26
  • Late to the party - This is great! I was wondering though if I can expand this as many times needed, or if this method only works well with say two conditions? (Basically, could I do a formula like this: `=MAX(IF((A1:A6=A10)*(B1:B6=B10)*(C1:C6=C10)*(D1:D6=D10)*(E1:E6=E10),C1:F6))`?) – BruceWayne May 18 '16 at 20:38
0

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.

Baum mit Augen
  • 49,044
  • 25
  • 144
  • 182
Mehdi Babapour
  • 1