Calculating Next Delivery Date Based on Conditions

I'm looking to create a formula which will populate the next delivery date and time base on a set of conditions.

Details:
Worksheet 1 contains a listing of scheduled incoming containers, each with a unique container number, port of entry, delivery date, and time.

Worksheet 2 is a worksheet used to track containers incoming to the facility. Columns include status, a unique container reference number, and port of entry.

Need:
Once the container's status changes to "Complete" on worksheet2, the formula needs to match the completed container with an incoming scheduled container.

Caveats: Containers must go back to the same port they originated from. Also, the next delivery date should reflect the next date and time based on the current date and time (meaning cannot be in the past), as there can be multiple containers scheduled to come in, the same day/week.

Attached are images of worksheet 1 & 2 for reference.Worksheet 1.JPG Worksheet 2.JPG

Thanks in advance for any advice or suggestions you might have!

Please post a small file with sample solutions.

Hi John, attached is the file as requested.

Update: I've gotten far enough to get the date and time to populate in a small sampling within the attached. The issue remains of how to trigger it to only pull dates that are beyond the current date and time stamp, as I don't want it to pull past dates from the listing. I'm thinking I need to use the NOW function somehow.

Thoughts?

Something like this ...

in D2

=IF(A2="EMPTY",IF(INDEX('Worksheet 1'!C:C,MATCH(C2,'Worksheet 1'!B:B,0),0)>=TODAY(),INDEX('Worksheet 1'!C:C,MATCH(C2,'Worksheet 1'!B:B,0),0),"No container"))

in E2

=IFERROR(IF(A2="EMPTY",INDEX('Worksheet 1'!D:D,MATCH(D2,'Worksheet 1'!C:C,0),0)),"")

I've tried the formula above but keep getting the return of "FALSE"

I've come up with the array formula below but need to add in an additional function to limit the results to match back to $C2. Any thoughts?

=MIN(IF('Worksheet 1'!C:C>=TODAY(),'Worksheet 1'!C:C,""))