Lookup latest date, but dealing with multiple values

Hi - I have the attached data.  The data on left represents the source data (shaded yellow), and the data to the right (shaded all blue) represents the desired result using some form of lookup.   I...