Looking for the most recent submission based off date

Ali Simpson

hello I am looking to build a formula that pulls the "Mid/ EOC Rating" from a reference sheet with the criteria that the ID's match, it is the most recent submission based on Evaluation Date, and that the Evaluation was an EOC not any other options

So far I have gotten to the below formula but it is still reading as invalid column value as it pulls up the current date today

=MAX({Tech Evaluations from Ships Range Date of Eval}, INDEX(COLLECT({Tech Evaluations from Ships Range Rate}, {Tech Evaluations from Ships Range ID number}, [Employee ID]@row), 1), " ")

Reference sheet: could have multiple submissions for each person

ChristianFinke

Hi @Ali Simpson

To pull the most recent "Mid/EOC Rating" for a specific ID based on matching ID, the latest Evaluation Date, and only for "EOC" evaluations, try this formula:
=INDEX(COLLECT({Tech Evaluations from Ships Range Mid/EOC Rating},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC",
{Tech Evaluations from Ships Range Date of Eval}, MAX(COLLECT({Tech Evaluations from Ships Range Date of Eval},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC"))), 1)

Let me know if this works and if you have any further questions.