Array formula reference (Corrected)

  • Thread starter Thread starter JAK
  • Start date Start date
J

JAK

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?
 
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
 
=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.)
 
=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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array formula reference 3
Reference for MAXIF array formula 7
Looking Up Only <>"" 2
How to use vlookup in an array formula 2
Array "sumif" 3
Excel array formulas 1
Find Max and Min based on cell reference 3
Find data in Range, Return Cell Reference 7

Back
Top