The formula below works fine, with every tested condition except one, when BG40=3 and F5=3 in sheet1 and all other conditions in the formula are true, then the result should be true, but it is false.
=AND(NOT(ISERROR(VLOOKUP(BC40,Sheet1!A:A,1,FALSE))),NOT(ISERROR(VLOOKUP(BD40,Sheet1!B:B,1,FALSE))),NOT(ISERROR(VLOOKUP(BE40,Sheet1!C:C,1,FALSE))),NOT(ISERROR(VLOOKUP(BF40,Sheet1!D:D,1,FALSE))),NOT(ISERROR(VLOOKUP(BG40,Sheet1!E:E,1,FALSE))),NOT(ISERROR(VLOOKUP(BH40,Sheet1!F:F,1,FALSE))))
=AND(NOT(ISERROR(VLOOKUP(BC40,Sheet1!A:A,1,FALSE))),NOT(ISERROR(VLOOKUP(BD40,Sheet1!B:B,1,FALSE))),NOT(ISERROR(VLOOKUP(BE40,Sheet1!C:C,1,FALSE))),NOT(ISERROR(VLOOKUP(BF40,Sheet1!D:D,1,FALSE))),NOT(ISERROR(VLOOKUP(BG40,Sheet1!E:E,1,FALSE))),NOT(ISERROR(VLOOKUP(BH40,Sheet1!F:F,1,FALSE))))