MIN & Colum Name

[shlomek12]

I have the following data

Item # Vendor 1 Vendor 2 Vendor 3

51645 31.99 33.99 44.99

C1823 36.99 23.99 51.84

Q6511 115.49 120.99 101.99

Now I need the MIN from Each Row plus I need the Vendor Name who is also the lowest price from this row

I hope someone can help me as I need this urgent please

 

[Matty]

Try like this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:46px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:49px;"><col style="width:80px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; ">Item #</td><td style="font-weight:bold; ">Vendor 1</td><td style="font-weight:bold; ">Vendor 2</td><td style="font-weight:bold; ">Vendor 3</td><td style="font-weight:bold; ">Min</td><td style="font-weight:bold; ">Min Vendor</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:left; ">51645</td><td style="text-align:right; ">31.99</td><td style="text-align:right; ">33.99</td><td style="text-align:right; ">44.99</td><td style="text-align:right; ">31.99</td><td>Vendor 1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>C1823</td><td style="text-align:right; ">36.99</td><td style="text-align:right; ">23.99</td><td style="text-align:right; ">51.84</td><td style="text-align:right; ">23.99</td><td>Vendor 2</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Q6511</td><td style="text-align:right; ">115.49</td><td style="text-align:right; ">120.99</td><td style="text-align:right; ">101.99</td><td style="text-align:right; ">101.99</td><td>Vendor 3</td></tr></tbody></table>
Formula in E2:

=MIN(B2:D2)

Formula in F2:

=INDEX(B$1:D$1,MATCH(E2,B2:D2,0))

Both of which would then need copying down.

But what should happen if more than one Vendor is selling for the exact same min price?

Mattyhttp://www.excel-jeanie-html.de/index.php?f=1

 

[Matty]

Referring to my last question, you would overcome this as follows:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:46px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:49px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; ">Item #</td><td style="font-weight:bold; ">Vendor 1</td><td style="font-weight:bold; ">Vendor 2</td><td style="font-weight:bold; ">Vendor 3</td><td style="font-weight:bold; ">Min</td><td style="font-weight:bold; ">Min Vendor</td><td style="font-weight:bold; ">Min Vendor</td><td style="font-weight:bold; ">Min Vendor</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:left; ">51645</td><td style="text-align:right; ">31.99</td><td style="text-align:right; ">33.99</td><td style="text-align:right; ">31.99</td><td style="text-align:right; ">31.99</td><td>Vendor 1</td><td>Vendor 3</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>C1823</td><td style="text-align:right; ">36.99</td><td style="text-align:right; ">23.99</td><td style="text-align:right; ">51.84</td><td style="text-align:right; ">23.99</td><td>Vendor 2</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Q6511</td><td style="text-align:right; ">101.99</td><td style="text-align:right; ">120.99</td><td style="text-align:right; ">101.99</td><td style="text-align:right; ">101.99</td><td>Vendor 1</td><td>Vendor 3</td><td>
</td></tr></tbody></table>
Formula now in F2 is:

=IF(COLUMNS($F2:F2)<=COUNTIF($B2:$D2,$E2),INDEX($B$1:$D$1,SMALL(IF($B2:$D2=$E2,COLUMN($B2:$D2)-COLUMN($B2)+1),COLUMNS($F2:F2))),"")

Which is an array formula and therefore needs committing with CTRL+SHIFT+ENTER. It can then be copied down and across.

Matty

 

[shlomek12]

Thank You Matty for your help

in my case there is always 1 lowest, so I will go with your first answer since it is not a array formula