Formula help to choose lowest cost from multiple vendors and then choose vendor

Hello everyone,

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!

test.xlsx

Thank you! Glad to be here and appreciate the help!

The only issue that results from the new formula is that when there are two vendors with the same lowest cost, this formula chooses the left most lowest cost vendor. In these situations, we would like it to display an error message so that we can then manually choose the preferred vendor. Is this possible?

Thanks again! This is working almost exactly like we would want!

The only issue is the following, which is what we have been running into as well... As you can see on your sheet, on Row 2, 4 and 5, your formula has chosen Vendor 3. However, on those same rows, there are two vendors with the same "lowest cost" of $25.30. In these type of situations, is there a way that the formula can produce an error or produce a statement saying "Choose Vendor" for example, instead of automatically picking the left most lowest cost vendor?

The reasoning is that in these situations, our purchasing department can filter out these errors and manually choose the preferred vendors and then import that data into our system. However, if the formula automatically chooses between two vendors with the same lowest cost, like in the situations on Row 2, 4, and 5, then our purchasing department could miss evaluating significant data between the two vendors before making their final decision.

Thank you for your continued help and have a wonderful weekend!

You guys are amazing! This is perfect and works exactly how we need it to. Thank you very much!