Need help with finding corresponding value for max value in the other column

[thlee1122]

Hi all,

Below is the part of the dataset I have.

As you can see, there are 4 columns: Model, Sequence, Applied Date, and Expected Cost.

Each model has different sequence numbers and each sequence numbers have different expected cost.

What I want to do is to extract only one corresponding sequence in the "Sequence" column for max value in the "Expected Cost" for each model using a formula.

For example,

Model: WT1701CV.ASSEEUS; Sequence: 2 (This model has 3 sequence number. I need to choose one sequence number that has highest expected cost. Sequence 2 has been chosen because sequence number 2 has largest expected cost with 116 among other sequence numbers, 2 and 3 for model, WT1701CV.ASSEEUS).

Model: WM3170CW.ABWEPUS; Sequence: 3

Model: WM4270HWA.ABWEPUS; Sequence: 2

Could you please tell me a formula that could provide this information?

Your help will be greatly appreciated.


[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Model[/TD]
[TD]Sequence[/TD]
[TD]Apply Date[/TD]
[TD]Expected Cost[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]WT1701CV.ASSEEUS[/TD]
[TD]2[/TD]
[TD]201506[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]201506[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]201506[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]WM3170CW.ABWEPUS[/TD]
[TD]2[/TD]
[TD]201506[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]201506[/TD]
[TD]568[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]WM4270HWA.ABWEPUS[/TD]
[TD]2[/TD]
[TD]201506[/TD]
[TD]276[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]201506[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]DLGX8001W.ABWEEUS[/TD]
[TD]2[/TD]
[TD]201506[/TD]
[TD]194[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]201506[/TD]
[TD]76[/TD]
[/TR]
</tbody>[/TABLE]

 

[123rickfear]

=IF(A2=A1,"",INDEX(B:B,MATCH(MAX(IF(A:A=A2,D:D)),D:D,0)))

confirmed with ctrl + shift + enter