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

Try below array formular:

Please Login or Register  to view this content.

ending with Ctrl-shift-enter

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.

OMG !!!

You are an absolute LEGEND. Thank you so much!