Return max value based on criteria

[Steven_TN]

Hello guys,

I am trying to get the max value based on the supplier's name and their (max) score that I have in a list.

in short: I have a list of suppliers (column A) to which I assigned a colored zone (B) and a score (C). Based on the supplier's name I would like to return the highest score that has been assigned to it, regardless of the zone's color. (I added it to the picture but maybe it wasn't necessary). so for instance, if you look at the picture attached you will see in column D the results I'd like to get. I have highlighted in pink the max values (C) for easy spotting

I have tried this formula but it doesn't work: =MAX(IF($B:$B=B2;$C:$C))
Other formulas also don't work for me.

I have excel 2013

thanks for your help!

 

[Fluff]

Did you confirm that formula with Ctrl Shift Enter, rather than just Enter?

 

[sandy666]

maaybe with Power Query

SUPPLIER	ZONE	SCORE		SUPPLIER	Max
SAFRAN	GREEN	80		SAFRAN	80
SAFRAN	GREEN	5		IVOPLAST	95
SAFRAN	GREEN	5		CAREMED	91
SAFRAN	RED	10
IVOPLAST	YELLOW	95
IVOPLAST	RED	5
CAREMED	RED	91
CAREMED	RED	45
CAREMED	RED	60
CAREMED	RED	80
CAREMED	GREEN	15

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SUPPLIER"}, {{"Max", each List.Max([SCORE]), type number}})
in
    Group

 

[Steven_TN]

Did you confirm that formula with Ctrl Shift Enter, rather than just Enter?

I did as you suggested and it works. thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.