Jump to content
Learn Chinese in China

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

Recommended Posts


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.


Link to post
Share on other sites
Site Sponsors:
Pleco for iPhone / Android iPhone & Android Chinese dictionary: camera & hand- writing input, flashcards, audio.
Study Chinese in Kunming 1-1 classes, qualified teachers and unique teaching methods in the Spring City.
Learn Chinese Characters Learn 2289 Chinese Characters in 90 Days with a Unique Flash Card System.
Hacking Chinese Tips and strategies for how to learn Chinese more efficiently
Popup Chinese Translator Understand Chinese inside any Windows application, website or PDF.
Chinese Grammar Wiki All Chinese grammar, organised by level, all in one place.


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. 



  • Like 1
Link to post
Share on other sites

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)



  • Like 1
Link to post
Share on other sites

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!🍻

Link to post
Share on other sites

Join the conversation

You can post now and select your username and password later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Click here to reply. Select text to quote.

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...