Index, Match, Min and Max question

[mgriffiths]

I'm trying to retrieve a date (in column A) that corresponds with a
Min and Max amount (in columns B). Basically I need to know when my
amounts hit their Highs and Lows. Can someone help me with this
please?

Thanks,
Maria

 

[Bernard Liengme]

Dates in column A; values in B
Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0))
Returns the date corresponding the max value in B
Is this what was needed?
best wishes

 

[mgriffiths]

Kind of... I need it to return the date corresponding to the max value
in B, C or D and cannot seem to come up with the formula to look at
all three columns.

I appreciate the help!

 

[T. Valko]

Try these array formulas**:

For the MAX:

=INDEX(A1:A10,MAX((B110=MAX(B110))*ROW(B110)-MIN(ROW(B110))+1))

Format the cell as DATE

Note that if there are duplicate MAX values the formula will return the date
for the *LAST* instance of the max value.

For the MIN:

=INDEX(A1:A10,MIN(IF(B110=MIN(B110),ROW(B110)-MIN(ROW(B110))+1)))

Format the cell as DATE

Note that if there are duplicate MIN values the formula will return the date
for the *FIRST* instance of the min value.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[mgriffiths]

Thanks!!! It works great, and I'd have never thought of doing it that
way!

 

[T. Valko]

You're welcome. Thanks for the feedback!