Matching between two sheets and find the closest time match

Hello arindam111 and Welcome to Excel Forum.
Could you explain why Oct. 21 is a closer match than Oct. 19 as they are both one day different from Oct. 20 It may have to do with the date and time column mentioned, but it is hard to know from information given.
It might be helpful to upload a sample workbook that demonstrates what you want to accomplish. To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

This proposal employs a helper column (Sheet2!C) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =B2-INDEX(Sheet1!B$2:B$10,MATCH(A2,Sheet1!A$2:A$10,0))
Columns A:B on the OP sheet are populated using: =IF(Sheet1!A2="","",Sheet1!A2)
Column C on the OP sheet is populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

It will be easier to help if you will upload a sample that displays the setup. Indicate which cells need to be populated using formula/code. Please manually include the expected data for those cells so that we will know if our formula/code is working correctly. Let us know if you have any questions.

Here is a fairly lengthy proposal for the tricky requirements.
1. Notice that on the File-2 sheet the range is changed to a table with two extra columns.
A. The three original columns are custom sorted first by Key and then by Departure.
B. The Connections column is populated using: =COUNTIFS('File-1'!A$2:A$20,A2,'File-1'!B$2:B$20,"<="&B2)
C. The Order column is populated using: =IF(OR(D2=0,COUNTIFS(A$2:A2,A2,D$2:D2,D2)>D2),0,COUNTIFS(A$2:A2,A2,D$2:D2,D2))
2. A helper range, which may be moved and/or hidden for aesthetic purposes, is placed in H:M
A. Column H is populated using: =IFERROR(INDEX(A$2:A$16,AGGREGATE(15,6,(ROW(A$2:A$16)-ROW(A$1))/(E$2:E$16>0),ROWS(A$1:A1))),"")
B. Column I is populated using: =IF(H2="","",AGGREGATE(15,6,('File-1'!$B$2:$B$13)/('File-1'!A$2:A$13=H2),COUNTIFS(H$2:H2,H2)))
C. Columns J:K are populated using: =IF(H2="","",INDEX(B$2:B$16,AGGREGATE(15,6,(ROW(A$2:A$16)-ROW(A$1))/($E$2:$E$16>0),ROWS(A$1:A1))))
D. Column L is populated using: =IF(H2="","",VALUE(MID(K2,3,4)))
E. Column M is populated using: =IF(H2="","",RANK.EQ(L2,L$2:L$16,1))
3. The output, highlighted in green, on the OP sheet is populated using: =IFERROR(INDEX('File-2'!H$2:H$16,MATCH(ROWS(A$1:A1),'File-2'!$M$2:$M$16,0)),"")
Perhaps one of the other contributors will be able to shorten the process.
Let us know if you have any questions.

We can try. Please re upload your the file attached to post #7 but with service values as described in post #10. Remember to include the expected outcome.
Let us know if you have any questions.
By the way, there is no need to quote whole posts on this site, in most cases just using Quick Reply is sufficient.

This is not quite as automatic as the earlier proposal.
1. On the File-2 sheet column D is manually filled 1...16 (many ways to do this).
2. The Connections and Oder columns are populated as described previously.
3. The Rank column is populated using: =IF(F2=0,"",1+COUNTIFS(F$2:F$16,"<>0",D$2:D$16,"<"&D2))
4. The entire range is then sorted by ascending departure dates.
On the OP sheet:
The Key, Departure and Series columns are populated using: =IFERROR(INDEX('File-2'!A$2:A$16,MATCH(ROWS(A$1:A1),'File-2'!$G$2:$G$16,0)),"")
Arrival: =IF(G2="","",AGGREGATE(15,6,('File-1'!$B$2:$B$13)/('File-1'!A$2:A$13=G2),COUNTIFS(G$2:G2,G2)))
Let us know if you have any questions.

Perhaps the following will help.
On the 'File-2' sheet I1 is populated using: =COUNTA(A2:A100000)
Select cell D2 and type the number 1 > select the fill handle of cell D2 > right click the mouse > pull down then back up
A list of options should appear > select the last option 'Series'
In the 'Series' dialog box choose 'Columns' > type the number displayed in cell I1 into the 'Stop value:' window and select OK.
Select cell B2 and Sort A to Z (oldest to newest)
Let us know if you have any questions.

Could you tell us at which part of the suggestion in post #15 didn't work or, better yet, upload a sample of the file that demonstrates the difficulties that you are having.