find max..

[mycarpark]

assume we have the following lists...
How can i return max value of "id"?


e.g.
ia1 = 11
ia2 = 8
ia4 = 5
ib1 =7

id month
ia1 2
ia2 3
ia3 4
ia4 5
ia1 5
ia2 6
ib1 7
ib2 8
ib6 9
ia1 11

 

[Teethless mama]

=MAX(INDEX((A2:A11=D2)*B2:B11,))

 

[mikerickson]

The array formula =MAX(--(LEFT(A1:A10,2)="id")*(B1:B10)) might do what you want.
Or are you looking for

=INDEX(A:A,MAX(ROW(B1:B10)*(B1:B10=MAX(B1:B10))),1)

Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)