Problem with Indirect and Array Formula

[ihni]

I am having a problem getting this formula to work, i have 3 cells of text that at the beginning has a date in the same cell as the text.

eg;

A1: 9/26/2009 this is test A, B1: 10/10/09 This is test B, C1: 9/4/09 This is test C.

i am trying to find a way to fill a cell with the contents of the cell that has the latest date

i would like D1 to say "10/10/09 This is test B"

the formula i am using is
{=ADDRESS(1,MATCH(TEXT(MAX(DATEVALUE(TEXT(LEFT(A1:C1&"1/1/2009 ",SEARCH(" ",A1:C1&"1/1/2009 ")-1),"mm/dd/yyyy"))),"mm/dd/yy")&"*",TEXT(LEFT(A1:C1&"1/1/2009 ",SEARCH(" ",A1:C1&"1/1/2009 ")-1),"mm/dd/yy")&"*",0),4)}


the formula works fine and returns the address of the cell with the latest date.
if i then put in cell E1 the formula
=OFFSET(INDIRECT((D1)),ROW(E1)-1,0)
it gives me the result i want.

however if i try to combine the formula to find the cell with the latest date, within the offset/indirect formula, i receive an error "the formula you typed contains an error".

does anyone have any idea why this is, and how to fix it?

thank you

 

[sanrv1f]

Hello and welcome to the board,

What is your system date format?

 

[sanrv1f]

If your date format is mm/dd/yyyy, try this

B1

=INDEX($A$1:$A$3,MATCH(MAX(LEFT(A1:A3,SEARCH(" ",A1:A3)-1)+0),LEFT(A1:A3,SEARCH(" ",A1:A3)-1)+0,0))

with ctrl+Shift+Enter

 

[ihni]

Thank you very much sir, that response really helped

 

[ihni]

ok so i got the formula to work using the index function as you suggested, i kept my original code intact as the one you had given me would return an error if there was a blank cell in the array eg; (one row only had two values instead of three, or the values were in A1 and C1)

i am now using the formula

{=INDEX($A$1:$C$1,0,MATCH(TEXT(MAX(DATEVALUE(TEXT(LEFT(A1:C1&"1/1/2009 ",SEARCH(" ",A1:C1&"1/1/2009 ")-1),"mm/dd/yyyy"))),"mm/dd/yy")&"*",TEXT(LEFT(A1:C1&"1/1/2009 ",SEARCH(" ",A1:C1&"1/1/2009 ")-1),"mm/dd/yy")&"*",0))}

it works perfectly, except... i only want the formula to return a result if the cell next to it, E1, has a date. i tried putting it in a simple if statement but it is giving me an error "the formula you typed contains an error".

i appreciate your help