# If,and,or for one return result

1. ## If,and,or for one return result

Hello,

I am using the IF(AND(OR with (Search formulas and getting a #VALUE result.

If the search finds either the text/words "terminated", "superseded", "supersede", then I want the formula to return the text "Cancel & Replace"

``Please Login or Register  to view this content.``
Capture.PNG

I's rather use IFS, but I think IF(AND(OR will be better. Any help?

thanks

2. ## Re: If,and,or for one return result

Try this formula. It returns 0 if the word isn't found.
``Please Login or Register  to view this content.``
Does that give you the results you're looking for?

3. ## Re: If,and,or for one return result

You have two problems. First your boolean logic is not correct. You don't want to use an AND at all. You want an OR that includes three conditions, one for each word you're looking for.

Second, if SEARCH doesn't find what it's looking for it returns an error, which pollutes your entire result. You have to isolate each SEARCH call to prevent the error from propagating.

Here is a revision that works for your sample data:
Formula:
`Please Login or Register  to view this content.`

4. ## Re: If,and,or for one return result

The SEARCH function will return an error if the word is not found in A2, so you should construct it like this:

=ISNUMBER(SEARCH("Terminated",A2))

This will return TRUE or FALSE as appropriate.

Hope this helps.

Pete

5. ## Re: If,and,or for one return result

going to post mine because I worked on it anyway but it is the same as Jeff's...
=IF(OR(ISNUMBER(SEARCH("Terminated",A2)),ISNUMBER(SEARCH("superseded",A2)),ISNUMBER(SEARCH("supersede",A2))),"Cancel & Replace","")

6. ## Re: If,and,or for one return result

You don't need the AND and you probably need IFERROR around each SEARCH.

So, something like
Formula:
`Please Login or Register  to view this content.`

7. ## Re: If,and,or for one return result

Now I'm curious, as everyone else's suggestion includes supersede and superseded. Won't SEARCH("supersede",A2) find both supersede and superseded? Do you need both SEARCH calls?

8. ## Re: If,and,or for one return result

Melvosh, yes, supersede will pick up superseded, probably just correcting Shruder's original formula without thinking deeper about it.

9. ## Re: If,and,or for one return result

Thanks, just wanted to make sure I wasn't missing something obvious

10. ## Re: If,and,or for one return result

Hi, to all!

Another option could be:
=IF(COUNT(SEARCH({"supersede";"terminated"},A2)),"Cancel & Replace","")

Blessings!

11. ## Re: If,and,or for one return result

Jeff, your formula was perfect, thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)