MIN Function

[Wondering0407]

How do I attach the corresponding vendor name (column header) to a MIN
function value when comparing data in the same row horizontally from left to
right? I am trying to find the minimum price for 300 identical item
names(rows) from bids entered by 25 different vendors (columns). I am trying
to show the corresponding vendor name next to the minimum price.

My spreadsheet structure:
Item Name, Vendor 1 Price, Vendor 2 Price,..., Vendor 25 Price
Item 1, 0.10, 0.25,..., 0.07
....
Item 300, 0.99, 0.76,..., 0.85

Desired Result:
Item Name, Min Price, Min Vendor Name
Item 1, 0.07, Vendor 25
....
Item 300, 0.73, Vendor 2

Appreciate any info. Thanks.

 

[GaryDK]

Hi Wondering0407,

This should help. Assuming your bid table starts in cell A1 of Sheet1,
try the following formulas -

In the first cell below your desired result headers (Item name):
=Sheet1!A2
In the next cell to the right (Min Price):
=MIN(Sheet1!2:2)
And in the next cell (Min Vedor Name):
=INDEX(Sheet1!$1:$1,1,MATCH(B2,Sheet1!2:2,0))

I hope this helps,

Gary
(DropTwoThruFiveForDirext)

 

[Max]

This might suffice ..

Assuming the sample table below is
in Sheet1, in A1:E6

ItemN Vend1 Vend2 Vend3 Vend4
Item1 1.66 1.14 1.16 1.59
Item2 1.94 1.67 1.15 1.00
Item3 166 147 150 109
Item4 166 155 163 116
Item5 194 125 105 180

In Sheet2
------------
Assume the 5 items are listed in col A, A2 down
in the *same order* that they appear in Sheet1, viz.:

ItemN MinPrice MinVend
Item1
Item2
Item3
Item4
Item5

Put in B2: =MIN(Sheet1!B2:E2)

Put in C2:
=INDEX(Sheet1!$B$1:$E$1,MATCH(B2,Sheet1!B2:E2,0))

Select B2:C2 and copy down

You'll get:

ItemN MinPr MinVend
Item1 1.14 Vend2
Item2 1.00 Vend4
Item3 109.00 Vend4
Item4 116.00 Vend4
Item5 105.00 Vend3

Adapt to suit ..

(Note that its assumed there won't be any ties
in the minimum prices for any row)