Need next number

[Nadine]

I have the following information
Col A ColB ColC ColD Col E (where the formula needs to go
but showing what the result should be)
CA1234 75016 AA 1 5
CA1234 75016 AA 1 5
CA1234 75016 AA 1 5
CA1234 75016 AA 2 5
CA5678 75321 BB 4 4
CA5678 75321 BB 3 4
CA1234 75016 AA 5 5
CA5678 75321 CD 1 4
CA5678 75321 CD 4 4
CA5678 75321 BB 1 4
Col E should find the largest number in Col D that belongs to the
combination in the first 3 columns.
Any ideas on the formula to go in each line in col E?
Thank you.

 

[Max]

As responded to your multi-post in .worksheet.functions ...

Array-enter* into E1, copy down:
=MAX(IF((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C$10=C1),D$1$10))
*press CTRL+SHIFT+ENTER to confirm the formula

 

[steve]

Try in E1
=SUMPRODUCT(MAX((A1:A10=A1)*(B1:B10=B1)*(C1:C10=C1)*(D110)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.

Regards
Steve

 

[ncj]

Try in E1
=SUMPRODUCT(MAX((A1:A10=A1)*(B1:B10=B1)*(C1:C10=C1)*(D110)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.

Regards

Dear All,

In Steve's formula we need to freez the range - as
Try in E1
=SUMPRODUCT(MAX(($A$1:$A$10=A1)*($B$1:$B$10=B1)*($C$1:$C$10=C1)*($D
$1:$D$10)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.