I'd like to have vlookup return a name if a cell contains the word relief.
And also in consecutive rows. Meaning the word *relief* may be in e.g. 20
cells in a range of 1000. So something like:
vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on
another sheet.

And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these
results be put in rows 3 to 23, so I don't have to scroll down looking for
them.

Much thanks,

Steve

Auto Filter Custom Filter select contains type in relief OK out



"Steve" wrote:

I'd like to have vlookup return a name if a cell contains the word relief.
And also in consecutive rows. Meaning the word *relief* may be in e.g. 20
cells in a range of 1000. So something like:
vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on
another sheet.

And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these
results be put in rows 3 to 23, so I don't have to scroll down looking for
them.

Much thanks,

Steve

Ok, thanks,

That will get me all the 'reliefs' in order, but many of the reliefs are
duplicates,
meaning relief 1 might be Smith, and Jones, and Mason. My V lookup is
producing multi Smiths if Smith is the first it found matching relief 1. I
need the names of each match.
The B column has the filtered reliefs in it. Column 40 in the master tab has
the names I need.

Here' my vlookup
=VLOOKUP(B4,MasterTab!$O$21:$BE$982,40,FALSE)

Thanks,

Steve


"Teethless mama" wrote:

Auto Filter Custom Filter select contains type in relief OK out



"Steve" wrote:

I'd like to have vlookup return a name if a cell contains the word relief.
And also in consecutive rows. Meaning the word *relief* may be in e.g. 20
cells in a range of 1000. So something like:
vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on
another sheet.

And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these
results be put in rows 3 to 23, so I don't have to scroll down looking for
them.

Much thanks,

Steve

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.

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

The extra explanation clarifies things a bit, but the posted formulas
still work for me, if I understand the problem correctly. Could there
be typos?

Below is a slightly simplified version starting from a fresh sheet.

In the fresh sheet put this in A2:
=IF(ISERROR(FIND("relief",MasterTab!O21)),"",MAX(A $1:A1)+1)
and this in B2:
=IF(ROW()-1MAX(A:A),"",
OFFSET(MasterTab!$BB$21,MATCH(ROW()-1,A:A,0)-2,0))
then select A2:B2 and copy down to row 73.

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

With your latest data, I get 1 in A17 and 2 in A39, corresponding to
the rows in MasterTab!O21:O92 containing the word "relief" somewhere
in the text. The rest of column A is blank. Then, B2 reports what's in
MasterTab!BB36 and B3 reports what's in MasterTab!BB58. The rest of
column B is blank.

If it's still not working, there's a way to see step-by-step how Excel
is evaluating a formula. Select the cell containing a formula and use:
Tools Formula Auditing Evaluate formula
This might give a clue how to correct a formula.

(I have Excel 2003.)