=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!
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!