LOOKUP VALUE with IF

Hi guys,

I was wondering whether someone could help me with the below scenario which I am trying to look for solutions/alternatives.

For ease of reference I have attached a sample excel file.

For the sake of example, I have a cash management sheet which is recording the monthly cash inflows of commissions which are received by different individuals. Therefore each and every month, commission money is being transferred in as seen in column F.

After end of month, the amounts of commission which is received will be transferred to different bank accounts as seen in columns P:R. There will be 3 different transfers: commission bank account, petty cash account , or salaries account.

My focus is the commission. This has to be transferred within 7 days after month end. Therefore if month end is 28/02/2018 the exact amount of commission received for February will have to be transferred out by 07/03/2018.

The tricky part is that sometimes, an additional ad hoc transfer of commission might also be made within the same timeframe. Example as seen in February there were two transfers 386 and 20. The 386 is the exact amount of commission received in EUR terms. The 20 is a transfer which is done due to foreign exchange differences which might arise.

I would like to create a check in columns Z which would show whether the commission from previous month (e.g. if date is 07/02/2018, the commission of January amounting to 537) was transferred out or not by the deadline day. The formula I would like to create is that:
If in cell Y3, I have a date "07/02/2018" the formula would sum all the commission received for the month prior to that date i.e. from 01/01/2018 to 31/01/2018. Therefore 537. It would then lookup that 537 in column R. If the 537 is transferred by "07/02/2018" the formula should return the 537 if not a description saying "Commission hasn't yet been transferred".

Apologies for the long description.

Would really appreciate any ideas or help with the above

Many thanks

Keibri