Ok, thanks.

I think we're close, but I can't get it to work, because I don't think I
explained what I wanted properly. If I can begin again:

In a blank, fresh sheet I want:
to check the range on the "Master tab'! cells O21:O92, and if it finds the
word relief, give me the result of the 40th column (BB) from whatever row it
found the word relief. The cell it was found may be 'Master tab'!O36. So the
result of 'master tab'! BB36 I would want in row 2 of my fresh sheet.
The next find of relief might be in 'Master Tab'!O58. Give me the result of
the 'Master tab'! BB58, and put that result in row 3 in my fresh sheet, etc.
I hope this is a bit clearer.
I could enter the word relief anywhere on the new sheet if that would make
the lookup less wordy.
And thanks in advance for you patience.


Steve


"zvkmpw" wrote:

Presumably, B4 in the other sheet contains "relief."

Here's one approach with Excel 2003.

In the other sheet put this in C2:
=IF(ISNUMBER(FIND($B$4,MasterTab!O21)),MAX(C$1:C1) +1,"")
and this in D2:
=IF(ROW()-1MAX(C:C),"",
OFFSET(MasterTab!$O$21,MATCH(ROW()-1,C:C,0)-2,39))
then select C2:D2 and copy down for the length of the list.

Column D should contain the desired list. Column B is a helper column,
and can be hidden.
.