Index and Match to return the latest entry for a person by latest date.

Hi, I'm trying to do an index match formula to pull data into a sheet based on the persons name and latest entry for that person based on date.

Two sheets - Sheet one contains the latest rating for the employee, Sheet 2 contains the latest review date and status.

I want to lookup the "Archive Sheet" to get the latest rating entry for the employee.

This is my formula:

=IFERROR(INDEX({Archive Sheet - Engineering Range 1}, MATCH(MAX(COLLECT({Archive Sheet - Engineering Range 2}, {Archive Sheet - Engineering Range 3}, $Employee@row)), {Archive Sheet - Engineering Range 2}, 0)), "")

It seemed to be working until I had two employees with a review on the same date and it pulls the top entry.

Any ideas? Its not matching the employee name.


Thanks

Sarah

Best Answer

Answers