Compare date from 2 excel files

Hi all,

My situation currently is I need to compare date from 2 excel files. Excel(1) is the main excel file and excel (2) is the file that I want to input inside excel (1) after checking on several conditions i.e. Note: excel(2) may have more than 1 duplicates ID.

  1. If excel(2) date is within range of excel(1) start & end date. Then choose that row but if it has more than one ID that is within range then I need to choose the latest date between them.

I am able to check the within range date but I don’t know how to choose the latest date between them.

For better understanding:

Excel 1(Main file): where the result will be put in.
image

Excel 2(Input file):duplicate Service ID. I need to choose the green row to put into excel(1).
image