Index match max question

I have attached a file for review. Any help is appreciated. I have 3 worksheets. What I am trying to do is find the most current tenant that occupies a property by finding the most recent TEN KEY.

I have tried coming up with a formula to make this work for a couple days, using INDEX, MATCH and MAX- to no avail.

I hope what I'm asking makes sense. Please let me know. Thank you.