Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index - Offset - Match Issues | Excel Worksheet Functions | |||
Complicated Index Match Offset function | Excel Worksheet Functions | |||
Which to use - if, vlookup, match, index, offset, vba? | Excel Discussion (Misc queries) | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |