Selected Answer
I set up 3 named ranges as follows.
- SKU = A3:A12
- Prices = B3:E12
- Vendors = B2:E2
I use these names in my formulas below. You can replace the names with their actual addresses (use absolute addressing!) in the formulas or you can set up the same names in your workbook but make the addresses dynamic so that the ranges adjust automatically as you add or delete data rows or columns and you never need to change your formulas again.
First, here is the formula to be pasted to H3 in your sample, and copied down from there. If you paste it to other locations note that 3 indicates the row to which the formula must be pasted and G is the column in which the SKU number is found.
=SMALL(INDEX(Prices,MATCH($G3,SKU,0),0),1)
SMALL(blah,blah,blah, 1) returns the lowest price. Therefore SMALL(blah,blah,blah, 2) returns the second-lowest. Therefore the formula below goes into J3.
=SMALL(INDEX(Prices,MATCH($G3,SKU,0),0),2)
The third formula goes into I3. It extracts the vendor name from the Vendors range based on the price found. Copy it from there to K3 and down in both columns as far as required.
=INDEX(Vendors,MATCH(H3,INDEX(Prices,MATCH($G3,SKU,0),0),0))
This formula will give the wrong result if both the lowest and second-lowest prices are the same. Since it finds the vendor by looking at the price it can only find the first vendor if both prices are identical.
The cure for this discrepancy looks more severe than the fault. You would have to say something like "IF the vendor just determined is different from the vendor in column I, determine the vendor in the way you just did, Else determine the second vendor with the same price". The above formula becomes the test criterium and will have to be repeated to furnish the result if no action is needed. That doubles the size of formula plus a little extra for the IF() and reference ot column I, like =IF(A1 > 10, A1, 10).
Append to that a new formula to determine the second vendor. That formula would embed the same formula in a SMALL() function as explained here. This needs to be an array formula so that it can loop through the solutions it creates. The entire thing therefore would be about 4 times the size of the above and of array type.
Tell your preferred supplier to lower his price by a cent lol: