Lookup the first in a series of numbers.

[Jason]

I have a sheet of numbers (essentially a small database)
that is lined up by date. The dates are in column A and
the monthly returns of various investments are in columns
B:CW. All of the returns start on different dates. On
another sheet, I have an index function setup to lookup
the returns for any column of data in that I specify in
cell b1. The problem is that all of the series of data
start on different dates. So...what I am looking for is
a way to excel to pick the first value (non-blank cell)
out of a column within a range on my data sheet (as well
as identify in another cell the corresponding date (from
column A) that the first observation occurs. Any
thoughts would be appreciated. Many thanks.

 

[Tom Ogilvy]

If you want to use an array formula you can use:

=SMALL(IF($F$1:$F$1000<>"",ROW($F$1:$F$1000),""),1)

to get the row number of the first cell that isn't empty.