Vlookup of specific text, then in consecutive rows

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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()-1>MAX(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
 
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()-1>MAX(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.)
 
Very nice. 95% there.
I'm only getting #'s up to 9 in the A column, and also the corresponding 9
names in B2:B10, so that part is working great.
However, I should have 22 incidents of relief instead of only 9. The reliefs
might be in any row from Mastertab!O21:O923
Also, I'm dragging A and B on the fresh sheet to row 904, because that's
where I'm getting the max ref to the row in the master tab where relief might
be. The formula in cell A904 refs mastertab!O923.
Also, the $'s in the B column formula are showing $BB$21 in cell B904. Could
$BB$21 in cell B904 be causing the problem, meaning should it have been
dragged without the $ ?

Yes, Excel 2003

Thanks again,

Steve
 
I solved it .

It was only finding the lower case "r"elief, and I had some upper case
"R"eliefs in the data also. By changing all the lower case r's to upper case
R's, it worked perfectly.
Not important, but just curious for future reference - Is there a way to
somehow wildcard that formula to also find any cased relief ?

Thanks again for all your patience.

Steve
 
Is there a way to somehow wildcard that formula
to also find any cased relief ?
Click to expand...

Yes, easily.

First, look at the formula for column A. It can be paraphrased this
way:
If
(a) the row is to be excluded
Then
(b) return ""
Otherwise
(c) return the next higher unused number.
In this example, (a) is the ISERROR(...) test. The (a) part of the
solution is the *only* thing that filters the original data so that
just the desired rows show up in the final list.

For different examples, (a) can be any test of any values in the row.
One can change only (a), leaving everything else the same, and achieve
all sorts of filtering possibilities.

For example, to make the test case-insensitive (a) might be:
ISERROR(FIND("relief",LOWER(MasterTab!O21)))

For another example, to disallow duplicate names, (a) might be:
OR(ISERROR(...),
COUNTIF(MasterTab!BB$21:BB21,MasterTab!BB21)>1)

Modify to suit.
 
For another example, to disallow duplicate names, (a) might be:
   ...
Click to expand...

Caution: this example has an error. It should check for duplicates
among the "relief" rows only, but it mistakenly looks at all the rows.
 
Back
Top