+ Reply to Thread
Results 1 to 3 of 3

Find text (multiple criteria) in multiple cells, return multiple values if true/false

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    Find text (multiple criteria) in multiple cells, return multiple values if true/false

    Okay. I am trying to determine why this formula is not working. (or if it's just not going to work at all)

    =IF(ISNUMBER(SEARCH("*facebook*",AM6:DO6)),"Facebook",IF(ISNUMBER(SEARCH("*bing*",AM6:DO6)),"Bing","Other"))

    In cells AM6:D06, there are several cells that may contain bing or FB, embeded in a URL string... or neither. I want the formula to yield bing, FB or "other".

    I ran this and it works, but only returns "other", will not find FB or bing. This formula is runinng in column A.
    Last edited by nik1703; 05-26-2015 at 12:14 PM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,443

    Re: Find text (multiple criteria) in multiple cells, return multiple values if true/false

    Search will never work, it searches in a string

    Please Login or Register  to view this content.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find text (multiple criteria) in multiple cells, return multiple values if true/false

    Quote Originally Posted by mehmetcik View Post
    Search will never work...
    Sure it will. But, not in the formula syntax that the OP has tried.

    You would have to do something like this:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH("facebook",AM6:DO6))),"Facebook",IF(SUMPRODUCT(--ISNUMBER(SEARCH("bing",AM6:DO6))),"Bing","Other"))

    However, using COUNTIF is more efficient:

    =IF(COUNTIF(AM6:DO6,"*facebook*"),"Facebook",IF(COUNTIF(AM6:DO6,"*bing*"),"Bing","Other"))

    But, could there be BOTH Facebook and Bing in the cells? If so, which ever one you test for first is the one that will be the result of the formula.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Replies: 6
    Last Post: 11-13-2014, 11:26 AM
  2. [SOLVED] Multiple TRUE,FALSE criteria
    By quibilty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2013, 11:47 PM
  3. [SOLVED] Return Text if True based on multiple criteria
    By JonnyMa in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-21-2013, 06:57 PM
  4. Replies: 4
    Last Post: 01-08-2013, 09:04 AM

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