Using MAX with OFFSET and MATCH

[Guest]

First thank you for any and all the help I can get.
Can MAX be used with OFFSET and MATCH?
I'm trying to find the maximum value based upon MATCHing the value in
anothercell. I have these values (very simplified):
A B
T600 11
T600 6
T600 15
T601 2
T601 12
The formulas I have tried are:
=MAX(OFFSET($A$1,MATCH($A1,$A$1:$A$5,0)-1,1,-1,-1))
=LARGE(INDEX(OFFSET($L$2,MATCH($L2,$L$2:$L$6,0)-1,1,-1,-1),1),1)
but end up with 11 for T600 and it should be 15.
Is there any way to do this?
Thank you in advance forthe help
Joe

 

[Bob Phillips]

=MAX(IF(A1:A20="T600",B1:B20))

which is an array formula, so commit with Ctrl-Shift-Enter.

 

[Guest]

Thanks Bob,
This works great. I guess I tried to over complicate things.

Joe

 

[Domenic]

The simplest way would be...

=MAX(IF(A1:A5="T600",B1:B5))

....confirmed with CONTROL+SHIFT+ENTER. But if you have a large
spreadsheet and Column A is sorted in ascending order, the following may
be more efficient...

D1: enter your criteria, such as T601

E1:

=MATCH(D1,$A$1:$A$5,0)-1

F1:

=MATCH(D1,$A$1:$A$5)-MATCH(D1,$A$1:$A$5,0)+1

G1:

=MAX(IF(OFFSET($A$1:$A$5,E1,0,F1)=D1,OFFSET($B$1:$B$5,E1,0,F1)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!