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

[xlmaniac]

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

Region	Vendor Name	String	Lead Time	PO Count	% Mix-PO Count	Desired Result
North	James Associate	NorthJames Associate	2	4	20%
North	James Associate	NorthJames Associate	4	10	50%	4
North	James Associate	NorthJames Associate	3	6	30%
South	Mascara	SouthMascara	5	18	60%	5
South	Mascara	SouthMascara	4	12	40%

<tbody>
</tbody>

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.