Return max value for cells with same value in other cell-CodePudding

In the example:

Reference	Value	Max
1	200	200
2	300	500
1	100	200
2	500	500

I need a formula for the Max column, so it returns the max value from column Value for the same Reference. Tried with MAXIFS, but no success.

CodePudding user response：

Formula for column C =MAXIFS(B:B,A:A,A1)

CodePudding user response：

There are multiple ways. Try-

=LARGE(IF($A$2:$A$5=A2,$B$2:$B$5,0),1) 'Will work on earlier version with Array entry.
=MAX(FILTER($B$2:$B$5,$A$2:$A$5=A2))
=MAXIFS($B$2:$B$5,$A$2:$A$5,A2)