The Highest Value Match

[Mand7]

Hi,

What formula could I use to get the values in "Highest Score by Country" shown below ?


Thank you!

Participant	Country	Participant Score	Highest Score by Country
Mario	Italy	4​	8​
Ben	USA	4​	7​
Hans	Germany	2​	6​
Greg	USA	5​	7​
Jules	France	2​	2​
Jurgen	Germany	6​	6​
Bob	USA	7​	7​
Jack	USA	6​	7​
Luigi	Italy	8​	8​
Paolo	Italy	3​	8​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Participant	Country	Participant Score	Highest Score by Country
2	Mario	Italy	4	8
3	Ben	USA	4	7
4	Hans	Germany	2	6
5	Greg	USA	5	7
6	Jules	France	2	2
7	Jurgen	Germany	6	6
8	Bob	USA	7	7
9	Jack	USA	6	7
10	Luigi	Italy	8	8
11	Paolo	Italy	3	8
Data

Cell Formulas
Range	Formula
D2:D11	D2	=MAX(IF($B$2:$B$11=B2,$C$2:$C$11,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mand7]

MAX(IF($B$2:$B$11=B2,$C$2:$C$11,0))

It's returning a 0.

 

[Fluff]

Did you confirm it with Ctrl Shift Enter?

 

[Mand7]

Yes

 

[Fluff]

It looks as though the values in col C are text & not numbers.

 

[Mand7]

You are correct. Your formula works! Thank you selfless sir.

 

[Fluff]

You're welcome & thanks for the feedback.