Adding criteria to an array formula

Hello guys!

I am seeking help here after being stuck on this for many many hours. I appreciate any help I can get. With that said because I am a beginner, I would prefer not to get answers that rely on VBA. VBA is still to complex for me to use, I prefer to learn and adapt standard formulas (the more I know how to do, the less i come and ask silly questions)

Anyways...I need to create an orders shipment report. Shipments go out once a week, as each week passes, one new report is created for that week. Because there are space restrictions, not all orders are shipped onetime, some orders must stay and be shipped the following week.

Ive added the below information to each respective sheet so that you do not have to switch back and forth between the forum and excel. Also if you wish, you can just go straight to the workbook and read there.

In sheets "Week 1" and "Week 2" we have the Orders table (left) and Departure Log table (right).
Notice week 1's departure date is 6-Dec and week 2's is 13-Dec. In the logs, notice that not all orders were shipped on their intended date, this is key for the criteria I am seeking to implement.

Sheet "Goal Report Week 2" is what the report for week 2 should look like (it was created manually to show the end goal, no formulas here)
Notice that all orders from week 2 are on the report, even the ones not yet shipped (status of "pending"). Also any order from week 1 that was shipped on 13-Dec (week 2's shipment date) is on the report (meaning that it does not contain all orders from week 1).

Sheet "Report Week 2" contains my working progress formula. I am only showing the New York section because the other locations will have the same formula respectively. To further simplify this and help us focus on one formula, we will ignore the status column. This is how far I've taken it. As you can see, I was able to fetch all the data from the current shipment (week 2) correctly (no help needed in this section of the formula). However, I am failing at fetching week 1's orders that were shipped on 13-Dec only. Because I do not know how to add the criteria where it checks if the order shipped on week 2's shipments date, I am getting all of the orders from week 1.

Thanks guys