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.

1	A	B	C	D
2	Model	Sequence	Apply Date	Expected Cost
3	WT1701CV.ASSEEUS	2	201506	116
4		3	201506	38
5		4	201506	45
6	WM3170CW.ABWEPUS	2	201506	17
7		3	201506	568
8	WM4270HWA.ABWEPUS	2	201506	276
9		3	201506	63
10	DLGX8001W.ABWEEUS	2	201506	194
11		3	201506	76

<tbody>
</tbody>

 

[123rickfear]

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

confirmed with ctrl + shift + enter