Excel - Formula Query: Search for and Return Value

  • Thread starter Thread starter Sue
  • Start date Start date
S

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
 
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,"")
 
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
 
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,""
Click to expand...

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
Click to expand...
or is there something better that will do the trick
Click to expand...

Perhaps you mean something lik

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