How to find the highest value in a group with MAXIFS
MAX gives the biggest number in a column. MAXIFS gives the biggest within one group only, here the top score for a single team.
The formula
=MAXIFS(B:B,A:A,A2) in column D
What each part does
MAXIFS
Finds the largest number among the rows that pass the test. It reads as: the numbers to search, then the column to test and what to look for.
B:B
The whole of column B, the scores being compared. Change the letter to wherever your numbers are.
A:A, A2
The test: keep only the rows whose team matches this row's team. Taking the team from A2 rather than typing a name means the same formula works for every row, and for your own teams.
How it works, and what to watch for
MAXIFS works like SUMIFS: the values first, then the group column and the group you want.
It wants Excel 2019 or later. Before that, the same answer comes from a MAX wrapped in an array, entered with Ctrl+Shift+Enter.
Swap it for MINIFS and you get the lowest in the group instead.
Questions
How do I get the lowest in the group?
Use MINIFS with exactly the same arguments. It is the mirror image of MAXIFS.
No MAXIFS on my version?
Use MAX with an IF inside, entered as an array with Ctrl+Shift+Enter: {=MAX(IF(team="North",score))}.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
