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
|