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

Search Function that returns an integer not #VALUE!
 

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

Search Function that returns an integer not #VALUE!
 

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