INDEX, MATCH, AND OFFSET... OH MY!

[Confused]

I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A1>0,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.

 

[Bob Phillips]

=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]

OMG! You're a GENIUS, Bob! Thank you so much! I knew there was someone
smarter than me out there that could help! Thank you thank you thank you. =)

 

[Confused]

Oooookay... so question number 2.

If I were to add a column in between A & B, lets say Column A1

Could I put a formula in D2, that would look up the name in C2 -
=INDEX(B1:B10,SMALL(IF(B1:B10<>"",ROW(B1:B10)),2))

And give me the related info in our new column A1?

 

[Confused]

Never mind... I worked it out. =)

Oooookay... so question number 2.

If I were to add a column in between A & B, lets say Column A1

Could I put a formula in D2, that would look up the name in C2 -
=INDEX(B1:B10,SMALL(IF(B1:B10<>"",ROW(B1:B10)),2))

And give me the related info in our new column A1?