In article ,
Eric wrote:

A related question is, can you think of any formula that will stop at the
first blank cell? So, for example, if A1:7 is:

5
3
4
(blank)
1
(blank)
7

The formula would return 4.

First define the following...

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=LOOKUP(BigNum,A1:INDEX(A1:INDEX(A:A,MATCH(BigNum, A:A)),MATCH(TRUE,A1:IND
EX(A:A,MATCH(BigNum,A:A))="",0)))

--
Domenic
http://www.xl-central.com