Select value based on most current date

Good morning all...or evening if your on the other side of the world ha.

What I have is around 4000 lines of data, each with an employee name and a date (among other information) I would like to run a macro that will use a common key from one sheet, and select the most current employee from another using the common key from the first sheet. Attached is an example.

Find most Current Example.xlsx

can anyone give me a hand with this?

Whoa...that has to be the longest formula I've ever seen lol ha. I do have some questions though. My example does not exactly match my real data, (column names and what not) and you seem to be referencing them with in the formula or am I reading it wrong? I tried to the best of my knowledge go back and correct them and I get a #NAME? error. Also, an I supposed to be entering the lines you have for the superlist, startdate and r_Number anywhere? I couldn't find a place where you did.

eh, im still lost, but I see what you are going for...kinda. and I think my example is making things harder when trying to apply it to my real work. attached is my real work.

Real data.xlsm