nested If logic isn't working, plz help...

[raj3076]

Please tell me what I am doing in below logic, somehow not able to get the result:

=IF((AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="Green"))),"Compliant",IF((AND(AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="Orange"))),(NOT(ISBLANK(N2)))),"Compliant",IF((AND(AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="RED"))),(NOT(ISBLANK(N2)))),"Compliant",IF((AND(AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="Yellow"))),(NOT(ISBLANK(N2)))),"Compliant",IF((AND(AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="NA"))),(NOT(ISBLANK(N2)))),"Compliant",IF((AND(AND((HLOOKUP(A1,$A$1:$F$2,2,0)="Yes"),(HLOOKUP(G1,G1:L2,2,0)="NULL"))),(NOT(ISBLANK(N2)))),"Compliant","Non Compliant"))))))


Sheet attached for the data.

[RobertMika]

Not sure which formula you are trying to check but:
SHeet1 A23 shoudl be:
=IF((AND(AND(OR(OR(OR(HLOOKUP(B20,G20:L21,2,0)={"Green","Orange","Red","Yellow"})))),NOT(ISBLANK(N21)))),"Compliant","Non Compliant")