Does this work in C2, copied down?

=IF(SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet1!$B$3:$B$11))*(Sheet1!$C$3:$L$11="PROBLEM")),"RED",IF(SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet1!$B$3:$B$11))*(Sheet1!$C$2:$L$2="AC")*ISNUMBER(Sheet1!$C$3:$L$11))=5,"BLUE",IF(SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet1!$B$3:$B$11))*(Sheet1!$C$2:$J$2="AC")*ISNUMBER(Sheet1!$C$3:$J$11)),"YELLOW","WHITE")))