Lookup the first in a series of numbers.

  • Thread starter Thread starter Jason
  • Start date Start date
J

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.
 
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.
 
Back
Top