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

Sarah Ryan

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

Paul Newcome

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)

Paul Newcome

You are going to need to add a date column to the sheet that you are pulling the data to. Put your MAX/COLLECT in this column to pull the most recent date for each person.

Then you can use

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

Sarah Ryan

Thanks Paul, i'm not looking to return the date, looking to return the value in the index column which is the employee rating

Paul Newcome

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)

Sarah Ryan

Fantastic, that worked, thank you so much!

Paul Newcome

Happy to help. 👍️