Maximum value for a sub-set of records

[JEM80]

Hi I am trying to find the maximum value for a sub-set of records (what I really need is a MAXIF function!).  For example see table below: the maximum score for Apple is 5 (Simon) and the maximum for pear is 16 (Louise)

Name                   Fruit                     Score

Ann                      Apple                    2

Peter                    Pear                       3

Ben                      Apple                    1

Louise                  Pear                      16

Simon                  Apple                    5

Any ideas gratefully received. I am using Excel for Mac v 16.52

[#2 · Accepted solution]

@JEM80 

See the attached sample workbook.

[#3]

Perfect thanks - I couldn't find this formula in the help

[#4]

@JEM80 

I do not work on Mac, but if you have no MAXIFS when

with

=AGGREGATE(14,6,1/($C$3:$C$7=$G3)*$E$3:$E$7,1)

=INDEX($B$3:$B$7, AGGREGATE(14,6,1/($C$3:$C$7=$G3)*(ROW($B$3:$B$7)-ROW($B$2)),1) )