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
-
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)
Answers
-
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)
-
Thanks Paul, i'm not looking to return the date, looking to return the value in the index column which is the employee rating
-
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)
-
Fantastic, that worked, thank you so much!
-
