lookup logic to interrogate specific exceptions

I need to create a formula in Sheet1 that flags Column D with a 'Y' or 'N' based on the following rules:

In Sheet1, for most of the data, this will satisfy: If Column A = Column A (Sheet 2), then Flag Column D with a 'Y' (else 'N').

However, there are some exceptions/instances where I need to review other columns along with Column A.

I attached a sample of this.

thank you!