If,and,or for one return result

**Shruder** · 04-26-2019, 02:27 PM

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

**Melvosh** · 04-26-2019, 02:35 PM

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?

**6StringJazzer** · 04-26-2019, 02:35 PM

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.

**Pete_UK** · 04-26-2019, 02:38 PM

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

**Sam Capricci** · 04-26-2019, 02:39 PM

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

**TMS** · 04-26-2019, 02:40 PM

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.

**Melvosh** · 04-26-2019, 02:42 PM

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?

**Sam Capricci** · 04-26-2019, 02:48 PM

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

**Melvosh** · 04-26-2019, 02:51 PM

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

**johnmpl** · 04-26-2019, 03:10 PM

Hi, to all!

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

Blessings!

**Shruder** · 04-26-2019, 04:08 PM

Jeff, your formula was perfect, thanks

If,and,or for one return result

LinkBack

Thread Tools

Rate This Thread

Display

If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Re: If,and,or for one return result

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] how to return the next result > zero

Return the earliest result and latest result with criteria

[SOLVED] VBA return result

[SOLVED] return number from column if number is greater than and return result from different cell.

[SOLVED] If AL = a value i need B1 to return a result

excel result return wrong calcuation result

Advanced formula - Return result & Show Cell Reference of result

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions