Find a cell with a word and then return the next cell down

[pgarcia]

Hello all,
I'm looking to find the word "Credit Card Information" in column A and then
return the value or text in the cell below it.
So, the word "Credit Card Information" is in cell A15, I then need to return
the value or text in cell A16. But, I would also need to return the value in
cells A17-A23.

But, the word "Credit Card Information" will not always be in cell A15, it
will move around in column A, but I will always need the next 8 cell data
below that.

And, if at all possible, I’m not looking for a VB code on this one.

Thanks

 

[Jim Thomlinson]

You can use a combination of match and offset. Place this formula in a cell
and drag down 8 rows...

=OFFSET(A1, MATCH("Credit Card Information",A:A, FALSE), 0, 1,1)

 

[Gary''s Student]

In D1 enter:

=MATCH("Credit Card Information",A1:A1000,0)

and in D2 thru D9 enter:

=INDIRECT("A" & D$1+ROW()-1)

 

[pgarcia]

Hay, good new. I think I've finally learned something thing off this
discussion group. Lets see what you think.
D157=â€Credit Card Informationâ€
D158==MATCH(D157,A:A,0)
E158==IF(ISERROR(INDEX(A:A,D158)),"",INDEX(A:A,D158))
Then D159=D158+1

 

[pgarcia]

Very cool, thanks. How does it work? Please

You can use a combination of match and offset. Place this formula in a cell
and drag down 8 rows...

=OFFSET(A1, MATCH("Credit Card Information",A:A, FALSE), 0, 1,1)

 

[Jim Thomlinson]

Offset takes a starting point, in this case A1 and it moves a number of rows
and columns away from that origin. It then resizes by a number of rows and
columsn to define the range.

In your case it starts at A1. It moves way from that origin by the number
returned from the match. 15 in your example. It goes 0 columns to the right
and grabs a range that is 1 cell by 1 cell in size. Dragging the formula down
just increments the A1 so it gets successive rows...