3 nested IFs causes error

[Bob]

I have the following formula:

=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info")))

As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.
Thanks for the help,
Bob

 

[Niek Otten]

Hi Bob,

<Excel allows up to 7 nested IF statements>

A common misunderstanding. Excel allows 7 nested functions, no matter what sort. You reached that limit in your first formula
version.
There usually are many possible workarounds. Start here:

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have the following formula:
|
|
=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info")))
|
| As soon as I add one more IF statement to the front of the formula:
|
|
=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"))))
|
| I get an error (and the FIND function is highlighted)! Can anyone tell me
| why? I thought Excel allows up to 7 nested IF statements. My formula has
| only 3.
| Thanks for the help,
| Bob
|

 

[Dave Peterson]

It's not 7 nested IF's. It's 7 levels of nesting--no matter what the function.


IF(
ISERROR(
LEFT(C8,
FIND("--",C8,10)-2)),
HYPERLINK("#"&
CELL("address",
INDEX('Activity

 

[Harlan Grove]

As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",
IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.

Others have already mentioned that the limit is 7 nested function
calls - ANY function calls. As for your particular formula, you could
try rewriting it, perhaps as

=IF(OR(ISBLANK($B8),ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0))),"",
HYPERLINK("#"&CELL("Address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(IF(COUNTIF(C8,"??????????*--*"),LEFT(C8,FIND("--",C8,10)-2),C8),
'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))

Note that only your first reference to cell C8 is column-absolute,
$C8, while all the others are fully relative, C8. Is that really what
you want?

Also, FIND("--",$C8,10)-2 looks like a bug because it ends the
substring *2* chars before the first hyphen. For example, if C8 were

1234567890--xyz

FIND("--",$C8,10)-2 would return 9, and LEFT($C8,FIND("--",$C8,10)-2)
would return "123456789" rather than "1234567890". Is that really what
you want?

 

[Bob]

Niek,
Thanks for the clarification. Now I understand why I received the error.
Bob

 

[Bob]

Dave,
Thanks for the clarification. Now I understand why I received the error
message.
Bob

 

[Bob]

Harlan,
Thanks for suggesting using the OR function.
Also, the string I'm searching for the "--" in looks like this:

C8 = 1234567890 -- xyz

So LEFT(C8,FIND("--",C8,10)-2) would return 1234567890, which is what I want.
Thanks again,
Bob