Compare Multiple Rows and Pick largest value from a Column

Hi Folks,

Greetings !.

Please see data in the attached image in Col A thru Col D. In Col E, I want to populate the max value for the same part # from Col C or Col D depending upon value in Col B.

I have included a sample of the expected results in Col E for illustration purposes.

For example Row 3,5,6 are all part # 1 with Col B =”Y”, suggesting they are interchangeable parts, therefore, I would like the formula to have ability to pick 10 (since its largest between 3,10,0) from Col C.

I have tried to put it in simple terms, but please let me know if you feel there is a gap in my explanation.
Capture5.PNG

Any help will be much appreciated
Thanks

TR