Help: I need a function for finding next cell vertically with value

T

tobriant

I need to create a function that will look at a cell two columns to left
and determine if the cell is blank. If the cell is blank, I need to
search vertically for the next cell containing a value above this cell.
The distance to the next cell containing a value will vary. If the
distance did not vary, I could do this with an ISBLANK function
embedded within an IF statement, but the variability has me a little
confused. Any suggestions would be welcomed?

Thanks!
 
D

Dave Peterson

I put this in F10.

=LOOKUP(2,1/(D$1:D10<>""),$D1:D10)

And it returned the bottommost value in D1:D10.

To avoid an error if there isn't any value:

=IF(COUNTA(D$1:D10)=0,"No values",LOOKUP(2,1/(D$1:D10<>""),D$1:D10))
 
Top