Find Max Date and the value

[Jen_T]

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]

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

 

[Jen_T]

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]

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)

If this post helps click Yes

 

[Jen_T]

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]

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

 

[Jen_T]

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]

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)

If this post helps click Yes

 

[Jacob Skaria]

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