Creating & Naming a Nested IF(ISERROR(SEARCH)) Statement

Posted by Kerry on August 02, 2001 9:26 AM

I have over 30 IF(ISERROR(SEARCH)) Statements that look like this:
IF(ISERROR(SEARCH("Subscriber port is not assignable",K2)),0,1).
I would like to nest them into one column. I have tried
=IF(ISERROR(SEARCH("Subscriber port is not assignable",K2)),
IF(ISERROR(SEARCH("ActivationRequestMessage",K2)),
IF(ISERROR(SEARCH("Subscriber port does not exist",K2))
,IF(ISERROR(SEARCH("Requested Mux port does not exist",K2))
,IF(ISERROR(SEARCH("Translation restricted",K2)),
IF(ISERROR(SEARCH("Translation failure",K2))
,"","Translation Failure"),"Translation Restricted"),
"Inventory not built in !ntegrator or built wrong in NCON")
,"Inventory not built in !ntegrator or built wrong in NCON"),
"Not Translated or Assigned"),"WLI")
Then placed this Function into cell V2 and Named it Fallout1.
I then repeated this 3 more times and saved it as Fallout2, Fallout3 and Fallout4.
I then tried the function =IF(Fallout1,Fallout2,Fallout3,Fallout4)which returns #VALUE ERROR.
Can anyone help with this?

Posted by Aladin Akyurek on August 02, 2001 12:21 PM

Kerry,

Care to provide the full list of these categories and associated diagnoses?

It appears that you test K2 and issue a "diagnostic" message. Is that right?

Aladin

Posted by Kerry on August 02, 2001 5:10 PM

This is all I can provide right know. Can you show me how to use these formulas in my question?

Posted by Aladin Akyurek on August 02, 2001 5:46 PM

If the answer to my last question is affirmative [ It appears that you test K2 and issue a "diagnostic" message. Is that right? ], then I'd suggest that you make a 2-column table like consisting of dignoses (is that what it is?) and messages. Select all the cells of this 2-column table and name the selected range e.g., MTABLE (for message table) via the Name Box. Also, select the cells of the first column and name this range e.g., DIAGNOSES.

Use the following formula (instead of a multitide of IF functions):

=IF(ISNUMBER(MATCH(K2,DIAGNOSES,0)),VLOOKUP(K2,MTABLE,2,0),"Unknown Problem")

Aladin