Find Max Date and the value

Jen_T

unread,

Oct 6, 2009, 5:43:02 AM10/6/09

to

I was wondering if this is possible in Excel 2007.
I have six columns of data,starting in columns AB labeled as follows:
Note column placements may change as data is added.

Letter Sent#1 - this has a "Yes" or "" Value
Letter Sent Date - Date Value
Letter Sent #2 - this has a "Yes' or "" Value
Letter Sent Date - Date Value
Letter Sent # 3 - this has a "Yes" or "" Value
Letter Sent Date - Date Value

I need to look at each date value and find the most recent and then pull in
which it was; Letter Sent #1, or #2 or #3?

Also if I need to change the format in the future for the Letter Sent #1, #2
and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula
provided need updated or would it not matter since it is loking at the
maximum date first?

Jacob Skaria

unread,

Oct 6, 2009, 6:43:02 AM10/6/09

to

Try

=MAX(AB2:AG2)
(and format the formula cell to excel date format...Right click
FormatCells>select Date and a format)

If this post helps click Yes
---------------
Jacob Skaria

Jen_T

unread,

Oct 6, 2009, 8:11:01 AM10/6/09

to

Hi Jacob, You are all over th epalce in here : )
Thank you for all your help.
How does the max function you provided pull in which Letter had the maximum
date too ?

Jacob Skaria

unread,

Oct 6, 2009, 8:29:01 AM10/6/09

to

Probably I have misunderstood your initial post; try the below which returns
the header in AB1:AG1 for the max date in row 2 AB2:AG2

=INDEX(AB1:AG1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1)

Jen_T

unread,

Oct 6, 2009, 8:31:02 AM10/6/09

to

These will be nonadjacent cells, how do I indicate this in the formula, I
thought I had mention but may not have, sorry.

Jacob Skaria

unread,

Oct 6, 2009, 8:38:01 AM10/6/09

to

ColAB ColAC ColAD ColAE ColAF ColAG
LS#1 Date1 LS#2 Date2 LS#3 Date3
Y 10/4/2009 Y 10/6/2009 Y 10/8/2009

=INDEX($AB$1:$AG$1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1)

I tried the formula with data as above...which is what I understood from
your initial post. In the aboave example the formula will return the header
"LS$3" since the date in Col AG is the highest...

If this post helps click Yes
---------------
Jacob Skaria

Jen_T

unread,

Oct 6, 2009, 8:47:01 AM10/6/09

to

I mistated, these will now be non adjacent cells. I apologize, so I will have
data lets, say in AD:AC, AE:AF, AH:AI, AK:AL

Jacob Skaria

unread,

Oct 6, 2009, 12:16:01 PM10/6/09

to

Try the below. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=INDEX($AC$1:$AL$1,MATCH(MAX(IF($AC$1:$AL$1="Letter Sent
Date",$AC$2:$AL$2)),$AC$2:$AL$2,0)-1)

Jacob Skaria

unread,

Oct 6, 2009, 12:22:01 PM10/6/09

to

The date field starts with 'Letter Sent Date' then try the below version
which search for this keywords and look for the dates in these columns
alone...

'(array entered)
=INDEX($AC$1:$AL$1,MATCH(MAX(IF(ISNUMBER(SEARCH("Letter Sent
Date",$AC$1:$AL$1)),$AC$2:$AL$2)),$AC$2:$AL$2,0)-1)

If this post helps click Yes
---------------
Jacob Skaria