Lookup value then return result from other column, varying row num

[Justin]

Hello,
I have used the excel discussion group to solve many problems, and now have
one that I need to post!

I have a report that exports data (actually in text format, but that may not
matter) where I need to look down column A and find the cells with "AxxxS"
where the xxx are numbers (well, text) in ascending order. In between the
"AxxxS" values are cells in column a with various meaningless text data.
What I need to find is the maximum value (remember these numbers are exported
to this file as text) in column H that is some rows down from the "AxxxS"
row. The number of rows down can vary, but it always will be the cell with
one row above the next "AxxxS" value.

There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and
the corresponding number in column H. Also, the number of rows between each
"AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other
values in column H, but the one I need is the maximum of those values before
the next "AxxxS" sequence.

I have been trying sumproduct and offset and other combinations, but can't
find a solution, hopefully someone can help!

Here is what the data looks like.

A B ---> H
1 A500S
2 othertext
3 othertext
4 othertext 3838
5 A501S
6 othertext
7 othertext 2500
8 A502S
9 othertext
10 othertext
11 othertext 3000
12 A503S
13 othertext
14 othertext
15 othertext
16 othertext 1000
17 A504S
18 othertext
19 othertext
20 othertext 1200


Want to return in columns J and K:

A500S 3838
A501S 2500
A502S 3000
A503S 1000
A504S 1200

 

[Max]

In I1:
=IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"")

In J1:
=IF(ROW()>COUNT(I:I),"",INDEX(A:A,SMALL(I:I,ROW())))

In K1:
=IF(J2="","",OFFSET($H$1,MATCH(J2,A:A,0)-2,))
Select I1:K1, copy down to last row of source data. Minimize/Hide away col
I. Cols J & K returns 99.99% desired results. For the last value extracted in
col J, do a manual check & overwrite the blank in col K.

 

[T. Valko]

1 A500S

=IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"")

It might be possible that "othertext" will meet those conditions. All of the
"codes" seem to be the same length so you might want to use that as
condition:

AND(LEN(A1)=5,LEFT(A1)&RIGHT(A1)="AS")
AND(LEN(A1)=5,REPLACE(A1,2,3,"")="AS")

Those could also fail!

 

[Max]

Wonder if the OP is interested in the responses given? In the absence of any
feedback from OP, looks like our efforts here responding to his posting
have/will only benefit all others, other than the OP.

 

[Justin]

Thanks Max! I have been away, so sorry about the late thanks. I was
bothered by the last cell, but found a way to get the last cell information
to automatically be shown (the report has cells in the very bottom that are
always the same, so I searched for those).

It works great, and T.Valko was right in that the returns may find other
data, but not in my case.

Thanks again!

Justin

 

[Max]

Welcome, good to hear that you got your issue resolved.
(I was just cruising by in the neighbourhood ..)