Pick up the Vendor code of a part code from looking the highest Qty of a part code

[PRADEEPB270]

I have an excel file having part code,name,vendor and Qty ( Quantity ).

My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,

For more clarification,please refer the attach file:-

[benishiryo]

hi Pradeep, try this array formula in I5:
=INDEX($E$5:$E$21,MATCH(H5&MAX(IF($C$5:$C$21=H5,$F$5:$F$21)),$C$5:$C$21&$F$5:$F$21,0))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

if you need to counter errors, this will work in Excel 2007:
=IFERROR(INDEX($E$5:$E$21,MATCH(H5&MAX(IF($C$5:$C$21=H5,$F$5:$F$21)),$C$5:$C$21&$F$5:$F$21,0)),"")

[PRADEEPB270]

Thank you for your nice formula.