Search Function that returns an integer not #VALUE!

Hi
I'm trying to use a logical function to search text in a cell, see
below

=IF(SEARCH("DENTAL",A3,0) = 0, IF(SEARCH("VISION",A3,0) =
0,"yes","no"),"no")


Basically I need a way to determine if the defined text exists in
string and if so return flag that record. I'm comfortable using
access instr() function but I have a large amount of non-indexed
information and I'm trying to create a rule table rather than churn
away via an ODBC connection from Access.

My problem is that the search function won't return an integer, just
#Value!. Is there a function that can look for #VALUE! I was
thinking that it might be like isNA()

I'm not stuck on using the search function so alternative solutions
are greatly appreciated.

Thanks

David Hallidy

Hi David, try this:

=IF(iserror(SEARCH("DENTAL",A3,0)),"no",IF(iserror (SEARCH("VISION",A3,0)),"no","yes"))

Hope this helps.

Pete

On Mar 28, 4:15 pm, "DavidH" wrote:
Hi
I'm trying to use a logical function to search text in a cell, see
below

=IF(SEARCH("DENTAL",A3,0) = 0, IF(SEARCH("VISION",A3,0) =
0,"yes","no"),"no")

Basically I need a way to determine if the defined text exists in
string and if so return flag that record. I'm comfortable using
access instr() function but I have a large amount of non-indexed
information and I'm trying to create a rule table rather than churn
away via an ODBC connection from Access.

My problem is that the search function won't return an integer, just
#Value!. Is there a function that can look for #VALUE! I was
thinking that it might be like isNA()

I'm not stuck on using the search function so alternative solutions
are greatly appreciated.

Thanks

David Hallidy

Let's see if I understand correctly

If Cell A3 contains either "Dental" or "Vision" (or both, presumably)
Return "No"
Otherwise, return "Yes"

If that's true, try this:
=IF(MAX(COUNTIF(A3,{"*Dental*","*Vision*"})),"No", "Yes")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"DavidH" wrote:

Hi
I'm trying to use a logical function to search text in a cell, see
below

=IF(SEARCH("DENTAL",A3,0) = 0, IF(SEARCH("VISION",A3,0) =
0,"yes","no"),"no")


Basically I need a way to determine if the defined text exists in
string and if so return flag that record. I'm comfortable using
access instr() function but I have a large amount of non-indexed
information and I'm trying to create a rule table rather than churn
away via an ODBC connection from Access.

My problem is that the search function won't return an integer, just
#Value!. Is there a function that can look for #VALUE! I was
thinking that it might be like isNA()

I'm not stuck on using the search function so alternative solutions
are greatly appreciated.

Thanks

David Hallidy