INDEX, MATCH, AND OFFSET... OH MY!
Never mind... I worked it out. =)
"Confused" wrote:
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" wrote:
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. =)
"Bob Phillips" wrote:
=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.
|