Excel - Formula Query: Search for and Return Value
 

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,"")


"Sue" wrote in message
om...
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

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

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

Sue wrote...
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?

Perhaps you mean something like

=IF(COUNTIF($B$5:$B$1000,"*CL*"),
VLOOKUP("*CL*",$B$5:$B$1000,1,0),"")
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!