Using MAX with OFFSET and MATCH
 

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 a
=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

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

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

--
HTH

Bob Phillips

"Joe Gieder" wrote in message
...
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 a
=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

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

Joe

"Bob Phillips" wrote:

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

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

--
HTH

Bob Phillips

"Joe Gieder" wrote in message
...
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 a
=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

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!

In article
"Joe Gieder" wrote:

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 a
=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