Search for string in range of cells 3

[kasparov]

This must be simple but I can't find an answer. I want to use a function to search for a string (it happens to be "Red") in a range of cells (C3:C100).

I can use SEARCH or FIND to search in one cell but these don't work with a range.

I tried LOOKUP (& VLOOKUP & HLOOKUP) but they don't seem to work either, e.g:

=VLOOKUP("Red",C3:C100,1,FALSE) or
=VLOOKUP("Red",C3:C100,"YES",FALSE)

It must be easy but I'm at a loss. I'd appreciate some help!

Thanks, Chris

 

[arthurbr]

Hi Chris,
and what do you want to do when the string is found?
Count the number of occurences?

 

[kasparov]

Hi arthurb - I want to put this function in the top cell of each column & I'll probably just return the value "Red" if there is a "Red" anywhere in the cells below (& I'll use conditional formatting to turn the cell red).

We're using this to monitor alerts from one of our systems - so if there's any errors the top cell will turn red & we don't have to look down the whole column to check if there are any errors (errors are reports with the word "Red").

Thanks

 

[arthurbr]

This thread will help you out :thread68-1203598
Just adapt Zathras' formula and apply conditional formatting to your cell : if value >0 ... the color you want
Cheers

André

 

[kasparov]

Thanks Andre - Unfortunately I've tried this but I can't get SEARCH to work with a range of cells.

Slimming it down if I try =SEARCH("Red",C3:C100) this returns #VALUE whether there is an instance of "Red" in the search range or not.

If I do =SEARCH("Red",C3) then it works (it returns either TRUE or FALSE).

Do you know how can I get SEARCH to work with a range of cells?

Thanks

 

[arthurbr]

Did you try the formula WITHOUT slimming it down, AND entering it as an array formula ( CTRL+SHIFT+ENTER instead of ENTER)?
Be assured that if Zathras thought it would work by slimming it down, he would have done so.
(I suspect SEARCH on itself is NOT an array formula.

 

[Fenrirshowl]

Try the MATCH function =Match("*Red*",C3:C100,0) and incorporate an ISERROR function to give something like

=If(IsError(Match("("*Red*",C3:C100,0)),"","Red")

as match will return an error value if red is not present.

(I think this is what you are after?)

Fen

 

[AnotherHiggins]

CHoggarth,

Try this:

[tab][COLOR=blue white]=CountIf(A1:A65536,"*red*")[/color]

The asterisks act as wild cards, so this will count any cell that contains the string "red". This has the benefit of not only telling you that there is a "red" somewhere in the list, but telling you how many there are.

FYI: The thread arthurbr pointed to uses an array formula. Read that thread again and do exactly what Zathras suggests. See Excel's help file for information about Array formulas.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kasparov]

Fen - Thanks - that did it (I'd never come across the MATCH funtion).

Andre - Thanks for your help - I also had never come across array formulas (lae). It looked like it was going to work (& I'm sure it would have) but I also needed to introduce a subclause to look for Yellow (warnings) as well & I was struggling to get that to work.

Thanks both for your time

 

[kasparov]

Thanks to all. 3 different ways to do what I wanted and I found out about array formalas.

Have some stars.

 

[DirkStruan]

Chris

Note that you can simply enter the functions within the conditional formatting itself rather than as a worksheet function and then have the formatting refer to the result.

i.e. if you were turning B2 red in colour just select B2, Format/conditional formatting then change the drop down on the left to Formula is... and enter =IF(COUNTIF(C3:C100,"=*red*")>0,1,0) as the condition (using John's answer as an example) and set what you want to happen on the true event.

D