Find the minimum price


I want to buy 500 products. According to our legislation, I have to ask 5 different suppliers for the price. Each supplier sent me a price for each product.

Row: product1, product 2, product3,... product500

Column: supplier1, supplier2, ..., supplier5

Please help me choose a function that will tell me which supplier is the cheapest (has minimum value) for each product.




Hi Kresnicka and welcome to the Forum

Assuming your suppliers will let you pick freely quantities from the list of product/prices, the lowest price for a given item is (e.g. for row 2):

You can get the cheapest supplier from row 1 (assuming that is your header row and has the real supplier names listed in columns B to F of that row) using the two functions INDEX and MATCH.

For a given row, the MATCH function is used to return the column containing the lowest price, relative to B2 (using a formula similar to the above). If it's in cell C2, say, the number would be 2 (B=1, C=2).

Then that number is used in the INDEX formula to return the value in cell number 2 in $B1:$F1 (which is supplier2). The formula (for row 2) looks like this:

but the $ signs mean you can just copy it down to where product500 is.- note that the INDEX function fixes the top row with two $ signs, to fix both row and colum (so $B$1:$F$1) for all rows where the formula is to return a supplier but the MATCH part only fixes the columns ($B2:$F2).

In the attached spreadsheet, you'll see it used in column J (with product and price shown in H and I) against dummy pricing in columns B to F.

INDEX and MATCH can be confusing at first but I suggest you follow one of Don's excellent tutorials on the combination e.g. Best Lookup Formula in Excel - Index and Match

Hope this works for you (and makes sense).



Iiiiiii, many thanks!!!!
Kresnicka Feb 23, '21 at 4:24 am
No problem Kresnika!

If that worked for you, kindly mark my Answer as "Selected".
John_Ru (rep: 792) Feb 23, '21 at 5:19 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login