Can this formula be simplified?

[rastak]

=IF(OR(ISNUMBER(SEARCH("*tul1*",N4)),ISNUMBER(SEARCH("*sk701*",N4)),ISNUMBER(SEARCH("*cv901*",N4)),ISNUMBER(SEARCH("*cv911*",N4)),ISNUMBER(SEARCH("*ts901*",N4)),ISNUMBER(SEARCH("*461*",N4)),
ISNUMBER(SEARCH("*462*",N4)),ISNUMBER(SEARCH("*463*",N4)),ISNUMBER(SEARCH("*465*",N4)),ISNUMBER(SEARCH("*466*",N4))),"IL1",
IF(OR(ISNUMBER(SEARCH("*tul2*",N4)),ISNUMBER(SEARCH("*ts902*",N4)),ISNUMBER(SEARCH("*cv903*",N4)),ISNUMBER(SEARCH("*cv905*",N4)),ISNUMBER(SEARCH("*cv912*",N4)),ISNUMBER(SEARCH("*ILCV-ts905*",N4)),
ISNUMBER(SEARCH("*661*",N4)),ISNUMBER(SEARCH("*663*",N4)),ISNUMBER(SEARCH("*667*",N4)),ISNUMBER(SEARCH("*sk702*",N4))),"IL2",
IF(OR(ISNUMBER(SEARCH("*tul3*",N4)),ISNUMBER(SEARCH("*ts908*",N4)),ISNUMBER(SEARCH("*cv908*",N4)),ISNUMBER(SEARCH("*cv906*",N4)),ISNUMBER(SEARCH("*cv916*",N4)),ISNUMBER(SEARCH("*ILCV-ts906*",N4)),
ISNUMBER(SEARCH("*561*",N4)),ISNUMBER(SEARCH("*563*",N4)),ISNUMBER(SEARCH("*567*",N4)),ISNUMBER(SEARCH("*562*",N4)),ISNUMBER(SEARCH("*sk704*",N4))),"IL3","check it")))

 

[snjpverma]

Try this,

=IF(OR(ISNUMBER(SEARCH({"tul1","sk701","cv901","cv911","ts901",461,462,463,465,466},N4))),"IL1",IF(OR(ISNUMBER(SEARCH({"tul2","ts902","cv903","cv905","cv912","ILCV-ts905",661,663,667,"sk702"},N4))),"IL2",IF(OR(ISNUMBER(SEARCH({"tul3","ts908","cv908","cv906","cv916","ILCV-ts906",561,563,567,562,"sk704"},N4))),"IL3","check it")))

 

[thisoldman]

You don't need the asterisks in your search strings. The asterisk stands for zero or more characters when used as a wildcard in a search term.

I did come up with an array formula that might be easier to read. You can try this.

Book1
	M	N
4	IL2	bbbtul2aaa
5	IL1	bbb462aaa
6	IL3	bbbcv906aaa
7	check it	xxx
Sheet1

Cell Formulas
Range	Formula
M4	{=IF(SUM(IFERROR(SEARCH({"tul1","sk701","cv901","cv911","ts901","461","462","463","465","466"},N4),0)),"IL1", IF(SUM(IFERROR(SEARCH({"tul2","ts902","cv903","cv905","cv912","ILCV-ts905","661","663","667","sk702"},N4),0)),"IL2", IF(SUM(IFERROR(SEARCH({"tul3","ts908","cv908","cv906","cv916","ILCV-ts906","561","563","567","562","sk704"},N4),0)), "IL3","check it")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pike]

or this

=IF(ISNUMBER(MATCH(1,INDEX(SEARCH({"*tul1*","*sk701*","*cv901*","*cv911*","*ts901*","*461*","*462*","*463*","*465*","*466*"},N4),),)),"IL1",IF(ISNUMBER(MATCH(1,INDEX(SEARCH({"*tul2*","*ts902*","*cv903*","*cv905*","*cv912*","*ILCV-ts905*","*661*","*663*","*667*","*sk702*"},N4),),)),"IL2", IF(ISNUMBER(MATCH(1,INDEX(SEARCH({"*tul3*","*ts908*","*cv908*","*cv906*","*cv916*","*ILCV-ts906*","*561*","*563*","*567*","*562*","*sk704*"},N4),),)),"IL3","check it")))

 

[rastak]

Try this,

=IF(OR(ISNUMBER(SEARCH({"tul1","sk701","cv901","cv911","ts901",461,462,463,465,466},N4))),"IL1",IF(OR(ISNUMBER(SEARCH({"tul2","ts902","cv903","cv905","cv912","ILCV-ts905",661,663,667,"sk702"},N4))),"IL2",IF(OR(ISNUMBER(SEARCH({"tul3","ts908","cv908","cv906","cv916","ILCV-ts906",561,563,567,562,"sk704"},N4))),"IL3","check it")))

this works great!