+ Reply to Thread
Results 1 to 11 of 11

If,and,or for one return result

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    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?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,554

    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: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    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. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,534

    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","")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,973

    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: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    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. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,534

    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. #9
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: If,and,or for one return result

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

  10. #10
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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. #11
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    114

    Re: If,and,or for one return result

    Jeff, your formula was perfect, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how to return the next result > zero
    By leslieann in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2018, 03:52 PM
  2. Return the earliest result and latest result with criteria
    By PFDave in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-26-2016, 07:20 AM
  3. [SOLVED] VBA return result
    By Burt_100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-10-2016, 11:23 AM
  4. Replies: 7
    Last Post: 12-06-2015, 05:40 PM
  5. [SOLVED] If AL = a value i need B1 to return a result
    By t.regi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-06-2015, 05:44 PM
  6. excel result return wrong calcuation result
    By garyww in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2006, 06:20 AM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1