Max if

[ikuogs]

I'm not sure if somebody had asked before, but I would like to get the maximum value based on certain condition.

Col C = name
Col J = amount
Col K = maximum amount by same name

For example, C1=C2=Tom, C3=C4=Jerry, J1=1, J2=2, J3=3, J4=4, what is the (array) formula for col K if I want the result: K1=K2=2, K3=K4=4?

Thanks!

[DonkeyOte]

sounds like homework ... but

=MAX(IF($C$1:$C$4=$C1,$J$1:$J$4))

confirmed with CTRL + SHIFT + ENTER

[ikuogs]

sounds like homework ... but

=MAX(IF($C$1:$C$4=$C1,$J$1:$J$4))

confirmed with CTRL + SHIFT + ENTER

Thanks, it works for cell K1, but not K2 onwards. Each cell in col K is still comparing the value in col C to C1 instead of the respective rows.

Btw it isn't homework. lol

[ikuogs]

I am quite certain that your proposed solution works, after googling and reading other posts. Is there something wrong with my Excel? I'm using Excel 2003 SP3.

[DonkeyOte]

I can only presume you applied the formula across the entire range (of K) simultaneously.

Clear the range in which the array is present - re-enter the formula but only for the first row and set the array -- once the array is set (encased within { }) copy and paste the formula over the remaining rows - in turn the relative references will update accordingly.

[ikuogs]

Thanks Donkey for your help! Your guess was right! Heehee

Problem solved!