Find 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]

In E1, array-entered, ie press CTRL+SHIFT+ENTER to confirm the
formula:
=INDEX(D$1$10,MATCH(MAX(IF((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C
$10=C1),D$1$10)),IF((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C$10=C1),D$1
$10),0))
Copy down

 

[Max]

Earlier index/match was overcooked, sorry
This one suffices, 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))