Finding the eligible minimum value - please help urgent thank you so much

Hi all,

I would appreciate if you could help out here.

I have a spreadsheet for the purpose on finding the minimum value/ lowest cost based on multiple criteria/ conditions.

We are outsourcing from a few different vendors for office furniture.

C column is the individual cost for CHAIR, G column is the individual cost for DESK, K column is the combo cost for BOTH CHAIR & DESK.

I used SMALL and COUNTIF function to find out the minimum values in C column (for cost of CHAIR), G column (for cost of DESK) and K column (for total/ combo cost of both CHAIR & DESK).

Therefore, $C$7 returns the lowest value of CHAIR, $C$11 returns the lowest value of DESK, $C$15 returns the lowest value of CHAIR & DESK.

Please see attached the sheet.

My main purposes are:

1. From B5 to F7, based on the input value of $C$7, find out the vendor with the lowest price from $C$18 to $C$23.
2. From B9 to F11, based on the input value of $C$9, find out the vendor with the lowest price from $G$18 to $G$23.
3. From B13 to F15, based on the input value of $C$15, find out the vendor with the lowest total price from $K$18 to $K$23.

I am using SMALL, COUNTIF and SUMIFS trying to find out the values, however it does give me some issues because of the uncertainty of the values:

1. Different vendors might have the same price for the same item. For example, both Vendor 1 and Vendor 2 might have the same price for Chair.
Question: How would I display multiple vendor's names if it is found more than one vendor has the same lowest price?

2. When considering buying chair and desk from the same vendor as a combo, SMALL function in column K will not work perfectly, because the lowest price in column K will not capture whether a vendor has quoted both items or not. For example, Vendor 3 has only quoted CHAIR but did not quote DESK. Even Vendor 3's price is lower than the other's quoted, using SMALL function with this returned value is not true, because Vendor 3's price is CHAIR only.
Question: Values in K column are the results of C column + G column. This kind of value is called Valid Value. If anything in C or G column is missing, and the value in K column is a result of any of the missed value, then it is called a Invalid Value. How to set up a condition to pick up the lowest Valid Value out of the K column? Which means, how to pick up a value that is calculated from C column + G column while both C & G <> "0" or blank?

Thank you so much again and I really wanted an answer for this solution with your input it's really critical to me....

Hi @ agadge123

I found a mistake in your formulas, so I modified them a bit, and added a little trick to have multiple vendors if they have de same minimum price.
I hope it does what you need