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?
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?
not sure if i am unstanding the question
=vlookup(max(a1:a99),a1:h99,4)
would give you the value in column d corresponding to the max in column a
not a professional, just trying to assist.....
Try something along these lines:
=INDEX(D1:D7,MATCH(MAX(IF(A1:A7>0,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 <at>yahoo<dot>com
----
"JAK" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
=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
=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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks