How To Pick-Up Value Conditionally Against Maximum % Mix?

[xlmaniac]

Dear All,
I do have a sample data-set across A1:F6 as follows:-

[TABLE="width: 615"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Vendor Name[/TD]
[TD]String[/TD]
[TD]Lead Time[/TD]
[TD]PO Count[/TD]
[TD]% Mix-PO Count[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]James Associate[/TD]
[TD]NorthJames Associate[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]James Associate[/TD]
[TD]NorthJames Associate[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]50%[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]James Associate[/TD]
[TD]NorthJames Associate[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]30%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Mascara[/TD]
[TD]SouthMascara[/TD]
[TD]5[/TD]
[TD]18[/TD]
[TD]60%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Mascara[/TD]
[TD]SouthMascara[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]40%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to populate the Leadtime against Same String with the highest % Mix(Po Count).
I have also put the desired result in column G.
For the 1^st Set, the formula needs to populate 4 in Cell G3, since %Mix(PO-Count) is the highest (50%)against the Region-Vendor String(NorthJamesAssociate) and corresponding Lead Time is 4.
Could somebody help me out with the formula pls?
Regards

 

[Marcelo Branco]

Try this array formula in G2 copied down
=IF(F2=MAX(IF($C$2:$C$6=C2,$F$2:$F$6)),D2,"")
Ctrl+Shift+Enter

M.

 

[xlmaniac]

Try this array formula in G2 copied down
=IF(F2=MAX(IF($C$2:$C$6=C2,$F$2:$F$6)),D2,"")
Ctrl+Shift+Enter

M.

Simply amazing solution Marcelo.
Thanks a lot for helping me out with the solution.
Really appreciate the same.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.