Excel - Formula Query: Search for and Return Value

[Sue]

hi there

I would like the formula below to retrieve and return any entry within
a list (in excel) that begins with [or contains] the text "CL"..(in
this instance) - if there is nothing it returns a blank..

=IF(ISNUMBER(SEARCHB("CL",B5,1)),B5,"")

However I can only get it to search 1 cell at a time (and only in
cells below that cell ref (ie: B5), not above it). Is it possible to
replace the cell value with a range (ie: B:B)...so it searches more
extensively?

or is there something better that will do the trick?

many thanks in advance
Sue

 

[Dave R.]

It works if you wrap sumproduct around it in order to search more than one
cell at a time;

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("CL",B5:B999))),B5,"")

 

[Jason Morin]

How about an AutoFilter?

1. Select your data.
2. Go to Data > Filter > AutoFilter
3. Select "Custom" on the drop-down menu for col. B.
4. Select "Equals" if not already selected and put "CL*"
without the quotes.
5. Press OK.

HTH
Jason
Atlanta, GA

 

[hrlngrv - ExcelForums.com]

Sue wrote..

I would like the formula below to retrieve and return any entr within
list (in excel) that begins with [or contains] the text "CL"..(i thi
instance) - if there is nothing it returns a blank.

=IF(ISNUMBER(SEARCHB("CL",B5,1)),B5,""

However I can only get it to search 1 cell at a time (and only i cell
below that cell ref (ie: B5), not above it). Is it possible t replace the cel
value with a range (ie: B:B)...so it searches more extensively

or is there something better that will do the trick

Perhaps you mean something lik

=IF(COUNTIF($B$5:$B$1000,"*CL*")
VLOOKUP("*CL*",$B$5:$B$1000,1,0),""