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.



Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Right, but to do what you want, you need to use a COLLECT function inside of a COLLECT function which is not possible. Instead you have to use a formula to pull the date FIRST into a helper column, and then you can reference this in the INDEX/COLLECT formula which pulls the rating. I also noticed that I missed the rating range in the initial INDEX/COLLECT formula (corrected here).

    =INDEX(COLLECT({Source Sheet Employee Rating Column}, {Source Sheet Name Column}, Employee@row, {Source Sheet Date Column}, [New Date Column]@row), 1)