I need help with figuring out a formula for the attached sheet. Our company has multiple vendors for the same part numbers. In the attached sheet, I laid out an example of how our master item list is represented. What we are trying to do is pull the lowest cost into the "Lowest Cost" cell which is pulling the data from Vendor 1 thru Vendor 18. Our difficulty comes in for the "Preferred Vendor" column. We would like a formula that takes that "Lowest Cost" and pulls the vendors name associated with that lowest cost. I used a Small formula for lowest cost and an Index formula for the Preferred Vendor. The attached sheet has the formula built in but I included them below.
For lowest cost: =SMALL(D2:U2,1)
For preferred vendor: =INDEX($D$1:$U$1,SUMPRODUCT((D2:U2=C2)*COLUMN(D2:U2))-COLUMN(C2:C2))
The problem is that the formula for Preferred Vendor sometimes picks up either the incorrect vendor, a vendor that shows no pricing, and occasionally the correct vendor. Also, some of our vendors may have exactly the same lowest cost. In that case, we are fine if the preferred vendor column shows #REF or an error.
Any help you can provide would be much appreciated. Thank you!