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

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, Im not looking for a VB code on this one.

Thanks

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

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)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

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, Im not looking for a VB code on this one.

Thanks

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

In D1 enter:

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

and in D2 thru D9 enter:

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

--
Gary''s Student - gsnu200800

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

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,D15 8))
Then D159=D158+1


"pgarcia" wrote:

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, Im not looking for a VB code on this one.

Thanks

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

Very cool, thanks. How does it work? Please

"Jim Thomlinson" wrote:

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)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

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, Im not looking for a VB code on this one.

Thanks

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

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...
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

Very cool, thanks. How does it work? Please

"Jim Thomlinson" wrote:

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)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

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, Im not looking for a VB code on this one.

Thanks