+ Reply to Thread
Results 1 to 3 of 3

Search text string and return multiple results depending on content of text string

  1. #1
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    Search text string and return multiple results depending on content of text string

    Country Task List_EXAMPLE.xlsx

    Dear all, I have tried many variations of FIND ISERROR OR ISBLANK etc. etc. combinations to no avail.
    Essentially, I need to search multiple text strings, and depending on the content, return another text string.
    This is a list of items that our international office sends to multiple countries, they write down the countries affected in a column, eg “GB, DE, FR” or leave it blank if all countries are affected.
    So if column B is blank, I need an “X” in column C to say – we need to action this item
    If column B contains “GB” in a text string of countries, I need an “X” in column C as again we need to action this item.
    If column B contains the text “all except” I need column C to say “CHECK!” as the Admin Assistant dealing with this week’s memo will need to manually check which counties are excluded (unless we can automate it to differentiate between say “all except DE, FR, ES, GB” meaning no “X” should be in column C, and “all except DE” meaning “GB” does need to action this item, so “X” should appear) Although I am content enough with a “CHECK!!” text flag to the secretary – I am struggling with this so the above may be a bit too far!!
    Please find attached example spreadsheet that highlights what I want to accomplish.

    Thanks in advance for your help.

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Search text string and return multiple results depending on content of text string

    I am not sure if this would work in Excel for MAC.

    In D2:
    =IF(ISNUMBER(SEARCH("all except",B2,1)),"CHECK!","X")

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Search text string and return multiple results depending on content of text string

    I think this would be more convenient if you use list of key words and list of returned values instead of keep changing formula each time for new criteria.

    In C2 and copy down

    =IFERROR(IF(B2="","X",LOOKUP(10^308,SEARCH($D$2:$D$3,B2),$E$2:$E$3)),"")

    or if you prefer key words and returned values built in formula use this:

    =IFERROR(IF(B2="","X",LOOKUP(10^308,SEARCH({"all except","GB"},B2),{"CHECK!","X"})),"")

    Row\Col
    A
    B
    C
    D
    E
    1
    Item
    Affected countries
    Action Team GB?
    Key words
    Returned Values
    2
    test item 1 only BE, DE, DK, FI, FR, GB, HU, IE, LT, NL, PL, RO, SE X all except CHECK!
    3
    test item 2 only DE, AT, NL, FR, ES, PT, IT, HR, SI GB X
    4
    test item 3 all except FR, ES, PT CHECK!
    5
    test item 4 X
    6
    test item 5 only GB X
    Last edited by AlKey; 05-14-2015 at 06:54 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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] Search for a text string and return given text string to adjacent cell
    By hecgroups in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2015, 04:50 AM
  2. Replies: 1
    Last Post: 04-08-2013, 07:07 PM
  3. Lookup then Search for text string and return multiple entries
    By tnewell2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-24-2012, 12:11 PM
  4. Replies: 2
    Last Post: 02-26-2008, 03:22 PM

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