Return Value based on Date Column, Primary Column MAX(Collect
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?
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!