Multiple ISNUMBER(SEARCH()) functions

[Bassteban]

Hi guys,

**If you know of any way to simplify said equation, that would also help as the number of characters for a formula in Excel 2003 is limited. What I'm trying to do is to have a function to locate a potential list of terms and attribute a specific qualifier if that term is found!***

So I'm working on an analysis and I would like to know if someone knows what's wrong with this one:

=IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$F$2,$K2))),'Terms and Equivalents'!$F$1,IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$G$2,$K2))),'Terms and Equivalents'!$G$1,IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$H$2,$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$H$3,$K2))),'Terms and Equivalents'!$H$1,IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$I$2,$K2))),'Terms and Equivalents'!$I$1,IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$J$2,$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$J$3,$K2))),'Terms and Equivalents'!$J$1,IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$K$2,$K2))),'Terms and Equivalents'!$K$1,"OTHER"))))))

Now a quick hint is that I I remove the underlined part, it works but otherwise it won't...

The issue has to do with the SEARCH() part of the last section.

Also, this equation on the adjacent column has no problems so I'd like to have your opinion:

=IF(OR(ISNUMBER(SEARCH('Terms and Equivalents'!$A$2,$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$A$3,$K2))),'Terms and Equivalents'!$A$1,IF(OR(ISNUMBER((SEARCH('Terms and Equivalents'!$B$2,Professionals!$K2))),ISNUMBER(SEARCH('Terms and Equivalents'!$B$3,Professionals!$K2))),'Terms and Equivalents'!$B$1,IF(OR(ISNUMBER((SEARCH('Terms and Equivalents'!$C$2,Professionals!$K2))),ISNUMBER(SEARCH('Terms and Equivalents'!$C$3,Professionals!$K2))),'Terms and Equivalents'!$C$1,IF(OR(ISNUMBER((SEARCH('Terms and Equivalents'!$D$2,Professionals!$K2))),ISNUMBER(SEARCH('Terms and Equivalents'!$D$3,Professionals!$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$D$4,Professionals!$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$D$5,Professionals!$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$D$6,Professionals!$K2)),ISNUMBER(SEARCH('Terms and Equivalents'!$D$7,Professionals!$K2)),),'Terms and Equivalents'!$D$1,IF(OR(ISNUMBER((SEARCH('Terms and Equivalents'!$E$2,Professionals!$K2)))),'Terms and Equivalents'!$E$1,"See next")))))

Thanks!

[Richard Buttrey]

Hi,

Upload your workbook so that we can see the request in context.
Explain in detail what you're trying to do, (there may be a better way than your compound IF formula), and show what result you expect to get from your function.

Regards