Return Value based on Date Column, Primary Column MAX(Collect

Leah Brown

Hey everyone, I need help I have a sheet that has students assessment submission. Students take the same assessment multiple times. I am wanting to pull the Final Grade of the most recent submission by Student. I have tried MAX(Collect and keep getting 0 I have also tried Index(Match and have been unsuccessful. I created a an example of the columns. In this example I would expect the value for kjdfue Most recent grade submission to be "Pass" based on their submission on 2/10/22. Can anyone help?

Genevieve P.

Hi @Leah Brown

There are a few ways we could do this; the easiest may be to add a helper column in your source sheet which identifies the Max Date, so that you can then use this in your formula.

For example, you could put this in a Checkbox Column:

=IF([Assessment Submit Date]@row = MAX(COLLECT([Assessment Submit Date]:[Assessment Submit Date], [Student ID]:[Student ID], [Student ID]@row)), 1, 0)

Then once you have this checkbox, you can use it as a criteria to filter for in your INDEX(COLLECT formula, something like this:

=INDEX(COLLECT({Final Grade Column}, {Student ID Column}, [Student ID]@row, {Max Date Checkbox Column}, 1), 1)

Cheers,

Genevieve