Excel - Index, Match, Min and Max question

Asked By mgriffith on 07-Nov-07 02:41 PM
I'm trying to retrieve a date (in column A) that corresponds with a
Min and Max amount (in columns B:D). Basically I need to know when my
amounts hit their Highs and Lows. Can someone help me with this
please?

Thanks,
Maria




Bernard Liengme replied on 07-Nov-07 03:30 PM
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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
mgriffith replied on 07-Nov-07 03:45 PM
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 replied on 07-Nov-07 04:54 PM
Try these array formulas**:

For the MAX:

=INDEX(A1:A10,MAX((B1:D10=MAX(B1:D10))*ROW(B1:D10)-MIN(ROW(B1:D10))+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(B1:D10=MIN(B1:D10),ROW(B1:D10)-MIN(ROW(B1:D10))+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)

--
Biff
Microsoft Excel MVP
mgriffith replied on 07-Nov-07 05:19 PM
Thanks!!! It works great, and I'd have never thought of doing it that
way!
T. Valko replied on 07-Nov-07 05:24 PM
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP