Find the minimum price

0

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.

Answer
Discuss

Answers

1

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):

=MIN(B2:F2)
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:

=INDEX($B$1:$F$1,1,MATCH(MIN($B2:$F2),$B2:$F2,0))
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).

Discuss

Discussion

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