# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### 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
1
Views
129
Replies
7
Views
583
Replies
1
Views
121
Replies
5
Views
214
Replies
3
Views
149

1,186,361
Messages
5,957,402
Members
438,304
Latest member
duck90

### 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.

### Which adblocker are you using?

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

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