Max lookup

[kac1125]

Looking for a formula that will pull the max value for multiple records. For example
I want column c to display the max value from coulumn B Referencing Column A. Thnak you in advance!

A	B	C
John	16	35
James	21	72
John	35	35
James	72	72

 

[Michael M]

Try using

=MAX(INDEX(($A$2:$A$5=A2)*$B$2:$B$5,0))

drag down as required

 

[kac1125]

Try using

=MAX(INDEX(($A$2:$A$5=A2)*$B$2:$B$5,0))

drag down as required

Perfect! thank you so much!

 

[Michael M]

glad to help...

 

[jasonb75]

You could also use MAXIFS if you have a new enough version of excel, which will be more efficient than the array already suggested if you have a lot of data.

=MAXIFS($B$2:$B$5,$A$2:$A$5,$A2)