Calling all Excel Wizards (return maximum value from repeat entries via XLOOKUP)

[歐博思]

I've got an XLOOKUP returning students' 'score' column via their 'class' and 'number' columns. But for this assignment, I'm allowing them to submit as many times as they'd like and I'll take the highest grade. I want XLOOKUP to return the highest grade instead of the first. 

 

I would just sort the data and run XLOOKUP in descending order based on 'score', but the lookup array is an automatically updating sheet with data coming in from a Forms document, so it would inevitably unsort itself rather quickly. 

 

I've briefly experimented so far with the MAX function nested into the XLOOKUP, but haven't seen any success with that so far, only returning #VALUE!

 

Here's the formula thus far, returning just the first grade they submit:

=XLOOKUP([@班級]&[@座號],'Response Collector.xlsx'!batman[Class]&'Response Collector.xlsx'!batman[Seat],'Response Collector.xlsx'!batman[Score])

 

Wasn't sure if this belonged in 'general non china' or 'chinese computing', but figured 'chinese computing' would be more likely to have Excel Wizards. Plus some of the formula has Chinese characters so strictly speaking it's chinese computing right ?

 

Anyone have any suggestions for this Excel Apprentice to try? I'm on Excel 365 for Windows.example excel.xlsx

Created an Excel file to better illustrate what I'm trying to accomplish.

 

[Dani_man]

I am not sure how to do it easily using a formula in Excel - in R you would have achieved this in a jiffy. 

You can do it with a pivot table, by applying the MAX function to the values. See screenshots of pivot settings and result. 

[Balthazar]

You can use MAXIFS for this. I attached a screenshot, where I use two criterias (name and class ID) to find corresponding max value from column F. (Unfortunately my Excel is in Norwegian so the formula line in the screenshot won't make much sense, but the syntax is really straightforward. See here and here)

 

[歐博思]

Thanks to both of you, and double thanks to Balthazar for giving me such high scores in that formula which I ended up incorporating perfectly for my case!?