Auto Commenting

Hi,

I have a list of criteria for a list of comments basically boils down to this

if the criteria on the 2nd and 3rd col are met, the comment will be that found on 1st col.

C Book B NT

Not in A B T

Other C S

Not at B C T

I want to be able to auto update these comments for sheets that start with the name BCD. In my attached spreadsheet, I have the raw sheets and the BCD sheets which are the end product, would appreciate whoever can help me thanks.