=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Confused" wrote in message
...
I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as
I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that
will
give me the 2nd cell in Column B, that will change as the information
changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when
A2
is removed.