Funciton to Lookup First Post in a Column

[Ladypep]

I have a worksheet in which I want to write a formula that will look in a
column of 5 rows and return to me the first date that is posted in that
column.

My column of cells consist of Cells C12, C13, C14, C15, and C16. This
column is a date column.

I want to write a formula in Cell A3 that tells Excel to look in C12 and
return the date in C12 to A3, if one exists there. If it does not, then I
want it to look on down to C13 and return that date, but if there is no date
there, then look in C14 and return that one, etc. all the way down to C16
(which will ALWAYS contain a date).

Basically, I want Excel to look in my range of C12:C16 and return to me the
very first cell that is not blank

Is there anyway to do this? I've tried several different formulas (even
using VLOOKUP) and I'm not having any luck.

Thanks,

 

[vezerid]

Hi,

You can use the following *array* formula (commit with
Shift+Ctrl+Enter)

=IF(SUM(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<>"",ROW(C12:C16)-ROW(C12)+1))),"")

HTH
Kostis Vezerides

 

[Ladypep]

Thanks for your help, although this did not work. It returns #VALUE! error
though I cut and pasted your formula into my cell. Hmmm...I'm at a loss.
Thanks again.

 

[Duke Carey]

It worked fine for me, so long as my dates were entered as date values, not
as text that looked like a date.

 

[vezerid]

As Duke says, the test for emptiness might be the problem. Replace the
formula with:

=IF(COUNTA(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<>"",ROW(C12:C16)-ROW(C12)+1))),"")

HTH
Kostis Vezerides

 

[Ladypep]

Thanks to both of you. I got it to work this time.