Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array formula reference (Corrected)
The array formula MAXIF correctly gives me the maximum
value of A in my table A1:h99, say A43. How can I find the corresponding value in column D, i.e. in this case D43? |
#2
|
|||
|
|||
Try something along these lines:
=INDEX(D1:D7,MATCH(MAX(IF(A1:A70,A1:A7)),A1:A7,0) ) (Array-entered) Adapt the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in my table A1:h99, say A43. How can I find the corresponding value in column D, i.e. in this case D43? |
#3
|
|||
|
|||
=INDEX(D1:D99,MIN(IF(A1:H99=MAX(A1:H99),ROW(A1:H99 ))))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) JAK wrote: The array formula MAXIF correctly gives me the maximum value of A in my table A1:h99, say A43. How can I find the corresponding value in column D, i.e. in this case D43? -- Dave Peterson |
#4
|
|||
|
|||
=INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1: A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000, "dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1) Your formula, as posted/written returns the maximum value in Column B for those rows matching the date. You would change the $B$1:$B$1000 to $D$1:$D$1000 if you want to find the row based on the maximum value in column D. The value returned is from column H. -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in my table A1:h99, say A43. How can I find the corresponding value in column D, i.e. in this case D43? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula reference | Excel Discussion (Misc queries) | |||
Array Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with array formula | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |