+ Reply to Thread
Results 1 to 7 of 7

Complex "IF" Formula to search for text

Hybrid View

  1. 08-22-2011, 11:29 PM #1
    ninti777
    ninti777 is offline
    Registered User
    Join Date
    08-12-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Complex "IF" Formula to search for text

    Hi there people,

    I am using excel 2003 and have devised the following formula - through a lot of trial and error :

    =IF(ISERROR(SEARCH("*cat*",$K9,1)),IF(ISERROR(SEARCH("*dog*",$K9,1)),IF(ISERROR(SEARCH("*horse*",$K9,1)),IF(ISERROR(SEARCH("*cow*",$K9,1)),IF(ISERROR(SEARCH("*chicken*",$K9,1)),"",".5"),".4"),".3"),".2"),".1")

    This is intended to search for the following words and apply the percentage relative to the word:

    Cat = 0.1
    Dog = 0.2
    Horse = 0.3
    Cow = 0.4
    Chicken = 0.5


    My problem is that I need to include another 4 words and percentages, but my formula breaks down if I add any more.

    Pig = 0.6
    Donkey = 0.7
    Elephant = 0.8
    Monkey = 0.9

    I understand there is a limitation in Excel 2003 with 7 nested "ifs" but if anyone can help me with a solution or other way of doing this, I would be eternally grateful (and my boss will love me!!!)

    Formula will be in column P
    Text to search will be in Column K – starting on Line 9 (must be able to copy formula down multiple lines)


    Thank you
    Reply With QuoteRegister To Reply
  2. 08-22-2011, 11:43 PM #2
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Complex "IF" Formula to search for text

    it is better for helper if you post a attached file with dummy data, especially for data in column K
    is that ok?
    Best regard, -)iger-/iger
    If you are pleased with a solution mark your post SOLVED.
    Reply With QuoteRegister To Reply
  3. 08-22-2011, 11:55 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,677

    Re: Complex "IF" Formula to search for text

    Try below array formular:

    =INDEX({0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9},,MIN(IF(ISERROR(SEARCH({"cat","dog","horse","cow","chicken","pig","donkey","elephan","monkey"},K9,1)),"",COLUMN($A:$I))))
    ending with Ctrl-shift-enter
    Reply With QuoteRegister To Reply
  4. 08-23-2011, 01:42 AM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex "IF" Formula to search for text

    ninti777,

    Similar to prior post and based on your original approach whereby associated value for the "first" string found is returned 

    =LOOKUP(9.99E+307,SEARCH($A$1:$A$9,$K9),$B$1:$B$9)
confirmed with Enter
    where A1:A9 holds keywords and B1:B9 associated numeric value - items in A1:B9 should be listed in Descending numerical order i.e. monkey (0.9) first and cat (0.1) last

    The above would return #N/A if no item found.
    Last edited by DonkeyOte; 08-23-2011 at 04:00 AM. Reason: original suggestion modified 06:55 UK time
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  5. 08-23-2011, 07:01 PM #5
    ninti777
    ninti777 is offline
    Registered User
    Join Date
    08-12-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Complex "IF" Formula to search for text

    Hey guys,

    thanks for answering my call! I have attached a dummy file so you can see what I am trying to do. The first sheet contains the actual percentages I would like assigned to each animal and the second sheet contains the template I am trying to create. This will be sent to my distributors to fill in columns that are not highlighted in blue so that I can correctly apply their rebate percentage (column P). Ideally I would love it if the last column could return blank if there is no data, same with column P. This way cell C5 will return a result - the blue highlighted columns will be password protected, so I need this to fill down the entire sheet. Hope this helps.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 08-24-2011, 01:36 AM #6
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex "IF" Formula to search for text

    If we assume the "animal" term is mutually exclusive (i.e. a max of one animal will appear in any given string in Col K on the template) then you have a few options:

    Option 1

    'Trading Terms'
A18: *
B18: 0

'Volume Rebate Template'
Q9:
=IF($B9="","",LOOKUP(9.99E+307,SEARCH('Trading Terms'!$A$18:$A$27,$K9&" "),'Trading Terms'!$B$18:$B$27))
copied down
    Option 2

    'Volume Rebate Template'
Q9:
=IF($B9="","",SUMPRODUCT(ISNUMBER(SEARCH('Trading Terms'!$A$19:$A$27,$K9))+0,'Trading Terms'!$B$19:$B$27))
copied down
    Reply With QuoteRegister To Reply
  7. 08-24-2011, 09:30 PM #7
    ninti777
    ninti777 is offline
    Registered User
    Join Date
    08-12-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Complex "IF" Formula to search for text

    OMG !!!

    You are an absolute LEGEND. Thank you so much!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1