Return Value Based Off Of MAX Date, With Specific Criteria.

MichaelNewkirk

Hey guys, I'm sure this is easy/right under my nose, but any help would be appreciated.

I have a master sheet that receives multiple evaluations for four different employees from their supervisors. Each of the four employees will have hundreds of evaluations sent to this sheet, with the date, their name, and their score in respective columns.

In a Sheet Summary field, how can I retrieve the most recent evaluation score for a specific employee, based off of the most recent date? Each employee is searchable, i.e "John Smith", because this sheet will only house evaluations for the same four employees.

Any help would be tremendously appreciated!

Paul Newcome

I would suggest a helper checkbox column with this in it:

=IF(Date@row = MAX(COLLECT(Date:Date, Name:Name, @cell = Name@row)), 1)

This will check the box for the most recent date. Then you can use an INDEX/COLLECT to pull in the score.

=INDEX(COLLECT(Score:Score, Name:Name, @cell = "John Smith", Helper:Helper, @cell = 1), 1)