More than 7 nested search if statments

[duckie123]

Hello,
I am having trouble figuring out how to search within the description below to find the type of animal. I have tried using If and Search functions but I know If statements can only be nested 7 times and there are more than 7 types of animals. Can anyone help me come up with a formula or macro to do this? Thanks in advance!

<table style="width: 220px; height: 301px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 92pt;" width="123" height="20">description</td> <td class="xl65" style="border-left: medium none; width: 73pt;" width="97">type of animal</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">brown dog</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">dog</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">orange cat</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">cat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">yellow bird</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">bird</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">chicken with spots</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">chicken</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">red fish</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">fish</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">small box turtle</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">turtle</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">gray and black cat</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">cat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">orange fish</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">fish</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">fuzzy hamster</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">hamster</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">brown mouse</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">mouse</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">gray dog</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">dog</td> </tr> </tbody></table>

 

[VoG]

Try this UDF in a standard module. Use it like

=animal(A2)

Function Animal(r As Range) As String
Dim i As Long
Dim Animals
Animals = Array("dog", "cat", "bird", "chicken", "fish", "turtle", "hamster", "mouse")
Animal = ""
For i = LBound(Animals) To UBound(Animals)
    If InStr(r.Value, Animals(i)) <> 0 Then
        Animal = Animals(i)
        Exit Function
    End If
Next i
End Function

 

[Sandeep Warrier]

Hi

If you have a list of animals, then,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">description </td><td style="font-weight: bold;;">type of animal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Animal List</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">brown dog </td><td style=";">dog</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">bird</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">orange cat </td><td style=";">cat</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">cat</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">yellow bird </td><td style=";">bird</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">chicken</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">chicken with spots </td><td style=";">chicken</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">dog</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">red fish </td><td style=";">fish</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">fish</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">small box turtle </td><td style=";">turtle</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">hamster</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">gray and black cat </td><td style=";">cat</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">mouse</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">orange fish </td><td style=";">fish</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">turtle</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">fuzzy hamster </td><td style=";">hamster</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">brown mouse </td><td style=";">mouse</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">gray dog </td><td style=";">dog</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.999E+307,SEARCH(<font color="Red">$E$2:$E$9,A2</font>),$E$2:$E$9</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

Just a word of caution. Depending on ..
- just what is in your animal list,
- what order they appear in that list and
- what could occur in the 'description column'
.. the suggested solutions could return
'cat' for 'eyecatching bird' or
'fish' for 'blue starfish'
neither of which is probably what you would want.

 

[duckie123]

Hi Everyone,
Thank you so much for responding to my post. I tried the lookup formula which works perfect except that I now need a number to be returned if a certain animal is found within a string. Is this possible? Thank you!!

<table width="412" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 73pt;" width="97"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 92pt;" width="123" height="20">description</td> <td class="xl65" style="border-left: medium none; width: 73pt;" width="97">number</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">brown dog</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td>
</td> <td>dog = 1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">orange cat</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td>
</td> <td>cat = 2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">yellow bird</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td>
</td> <td>bird = 3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">chicken with spots</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4</td> <td>
</td> <td>chicken = 4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">red fish</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5</td> <td>
</td> <td>fish = 5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">small box turtle</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">6</td> <td>
</td> <td>turtle = 6</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">gray and black cat</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td>
</td> <td>hamster = 7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">orange fish</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5</td> <td>
</td> <td>mouse = 8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">fuzzy hamster</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">7</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">brown mouse</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">8</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">gray dog</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[Blade Hunter]

What would you want a catfish to show up as?

Just curious, i'm not really adding value to the post

 

[ZVI]

Formula from winning solution by Barry Houdini at Mr. Excel Challenge of the month can be used:

Excel Workbook
	A	B	C	D	E
1	Description	Number		Animal List	Animal Num
2	brown dog	1		dog	1
3	orange cat	2		cat	2
4	yellow bird	3		bird	3
5	chicken with spots	4		chicken	4
6	red fish	5		fish	5
7	small box turtle	6		turtle	6
8	gray and black cat	2		hamster	7
9	orange fish	5		mouse	8
10	fuzzy hamster	7
11	brown mouse	8
12	gray dog	1
Sheet

 

[Peter_SSs]

Formula from winning solution by Barry Houdini at Mr. Excel Challenge of the month can be used:

.. with the same cautions I mentioned previously. For example, with this slightly different data, I expect that ..

the blue cell should be 3, not 9
the yellow cell should be 4, not 9
the green cell should be 5, not 7

Excel Workbook
	A	B	C	D	E
1	Description	Number		Animal List	Animal Num
2	brown dog	1		dog	1
3	orange catfish	9		hamster	2
4	yellow bird	4		catfish	3
5	chicken with spots	6		bird	4
6	eyecatching bird	9		starfish	5
7	red fish	7		chicken	6
8	small box turtle	8		fish	7
9	gray and black cat	9		turtle	8
10	orange fish	7		cat	9
11	blue starfish	7		mouse	10
12	brown mouse	10
13	gray dog	1
Animals

 

[ZVI]

.. with the same cautions I mentioned previously. For example, with this slightly different data, I expect that ..
the blue cell should be 3, not 9
the yellow cell should be 4, not 9
the green cell should be 5, not 7

Nice remark, Peter!

Seems the updated formula works properly:
=LOOKUP(2^15,SEARCH(" " & D$2:D$11 & " "," " & A2 & " "),E$2:E$11)

Regards,
Vlad

 

[Peter_SSs]

Much happier.