13 Nested IF statements not returning "False" when relevant values should return "a"

AB1984

New Member
Joined
May 12, 2016
Messages
17
=IF($I15=1,
IF(OR(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,3,0)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a",""),
IF($I15=2,IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a",
IF(OR(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a",
IF($I15=3,
IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a",
IF(OR(AND(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=""),VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a","")),
IF($I15=4,IF(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4,"a",""),
IF($I15="","",IF($I15=5,IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a",
IF(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,"a","")),"")))))),
IF($I15=5,
IF(OR(AND(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=""),VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a",
IF(VLOOKUP(J$5, $A$5:$E$68,3,FALSE)=$J$4,"a","")))))

The above formula is returning the correct values when $I15 = 1,2 and 5. However when the value in $I15= 3 or 4 and all other conditions are met the returned value is "FALSE" rather than the expected "a". I have attempted to use the formula evaluation tool and didn't find it much use.
I have double checked the lookups and they're all looking in the correct place.

Any help would be greatly appreciated!
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Its very ugly but heres where the problem lies:

=IF($I15=1,IF(OR(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,3,0)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a",""),IF($I15=2,IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a",IF(OR(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a",IF($I15=3, IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a", IF(OR(AND(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=""),VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a","")), IF($I15=4,IF(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4,"a",""),IF($I15="","",IF($I15=5,IF(AND(VLOOKUP(J$5,$A$5:$E$68,5,FALSE)="Y",(VLOOKUP(J$5,$A$5:$E$68,2,FALSE)=$J$4)),"a",IF(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,"a","")),"")))))), IF($I15=5, IF(OR(AND(VLOOKUP(J$5,$A$5:$E$68,3,FALSE)=$J$4,VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=""),VLOOKUP(J$5,$A$5:$E$68,4,FALSE)=$J$4),"a", IF(VLOOKUP(J$5, $A$5:$E$68,3,FALSE)=$J$4,"a","")),"ITS HERE!!!")))
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top