# minimum of an array

#### charijit

##### Board Regular
Dear All,
In column-A I have various items : a,b,c,d,e,r,v,a,b,d,t,h,e,r,v.
In column-B I have prices of items in Column-A : 1,3,4,5,5,6,3,4,6,7,8,3,4,5,6.
In column-C I have vendor names, who have supplied the items in Column-A : ww,ee,dd,ff,ee,ee,dd,ww,rr,zz,ss,ee,ee,rr,dd.

In a separate sheet I have listed the items nos. appearing in Column-A mentioned above. In the second column I wish the minimun price of the item to appear (since item is repeating and vendors may vary and thus their rates). In the third column I wish the vendor name corresponding to the minimun price to appear.
I seek all of your help.

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### acw

##### MrExcel MVP
Hi

Assuming that your data is in sheet 1 in the range A1:C15 and the output data is in sheet 2, with the unique list of items starting in A1.

In B1 array enter the formula
=MIN(IF(Sheet1!\$A\$1:\$A\$15=Sheet2!A1,Sheet1!\$B\$1:\$B\$15,999))

In C1 enter the formula
=INDEX(Sheet1!\$C\$1:\$C\$15,SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$15=Sheet2!A1),--(Sheet1!\$B\$1:\$B\$15=Sheet2!B1),ROW(Sheet1!\$A\$1:\$A\$15)))

Copy down as required.

The formula in B1 has as a default maximum 999. You will have to change this to reflect the range of your data.

Tony

#### charijit

##### Board Regular
Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.

#### ken2step

##### Well-known Member
I ran his formulas in simulation and they worked perfectly returning the b value as 3 and vendor ee. You must of entered something wrong.
Ken

btw Tony...nice formula. I needed to ad the 3 for the column argument for Indexing though.

#### SIXTH SENSE

##### Well-known Member

charijit said:
Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.
Note:

The formula is array entered
Enter them by pressing Cltrl+****+Enter
They should work

#### ken2step

##### Well-known Member
Hi Sixth Sense...doesn't this need something...

In C1 enter the formula
=INDEX(Sheet1!\$C\$1:\$C\$15,SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$15=Sheet2!A1),--(Sheet1!\$B\$1:\$B\$15=Sheet2!B1),ROW(Sheet1!\$A\$1:\$A\$15)),3)

Ken

#### SIXTH SENSE

##### Well-known Member

Hi ken!
The 3 is not needed because
your array is just single column
Code:
``Sheet1!\$C\$1:\$C\$15``
You only refer to column C.
If suppose you have this one
Code:
``Sheet1!\$A\$1:\$C\$15``
then you need the 3 to reffer to the third column
in the range you specified.

#### charijit

##### Board Regular
Ken,
I may have done mistakes, but its not working after several attempts.

#### ken2step

##### Well-known Member
Thanks Sixth Sense....I see now....when I was appling it my array used was A:C....I did not read his formula correctly....as usual! Those little details do make a difference huh!
Thanks for correcting me.... I should just read!
Ken

#### ken2step

##### Well-known Member
Hi charijit....try what Sixth Sense was eluding to.....make sure you CSE the formula in B1...works for me perfectly!
ken

Replies
3
Views
466
Replies
2
Views
101
Replies
3
Views
142
Replies
6
Views
365
Replies
7
Views
252

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,871
Messages
5,766,855
Members
425,380
Latest member
CubeGirl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back