# Finding min/max value from a list of items that contain duplicates

#### kumquattie

##### New Member
Hello everyone, I tried searching for a solution to this, but couldn't find exactly what I was looking for, so here I am. I have a list of thousands of items (with many duplicates) from different vendors and I need to be able to quickly pull the lowest cost of each item with the associated vendor to do a comparison.

Say this is my list of items:

 Item Cost Vendor Soccer ball \$2.00 Dicks Soccer ball \$3.50 Sports Authority Soccer ball \$2.80 Modell's Soccer ball \$3.00 Jane's Sporting goods Soccer ball \$1.90 Five Below Football \$1.75 Modell's Football \$2.15 Dicks Football \$2.80 Jane's Sporting goods Football \$1.90 Five Below Basketball \$4.50 Jane's Sporting goods Basketball \$4.00 Five Below Basketball \$6.00 Sports Authority Basketball \$3.75 Walmart Basketball \$5.25 Dicks Basketball \$5.00 Modell's

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I want to be able to populate this table:

 Item Lowest Cost Associated Vendor Soccer ball Football Basketball

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I can use MIN IF to find lowest cost, but I can't figure out how to pull the associated vendor without doing a Vlookup with 2 criteria. Is that my only option? Or is there an easier way to do both? I also tried a pivot table because I can pull the MIN costs per item that way, but once I add the vendors, it just gives me all the costs per vendor per item, I can't figure out how to only pull the minimum value's vendor.

Thanks for the help!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### FDibbins

##### Well-known Member
Based in your data table being in A:C...
 A​ B​ C​ 21​ Item Lowest Cost Associated Vendor 22​ Soccer ball 1.9 Five Below 23​ Football 1.75 Modell's 24​ Basketball 3.75 Walmart
B22=MIN(IF(\$A\$2:\$A\$16=\$A22,\$B\$2:\$B\$16))
ARRAY entered using CTRL SHIFT ENTER
C22=INDEX(\$C\$2:\$C\$16,MATCH(A22&" "&MIN(IF(\$A\$2:\$A\$16=\$A22,\$B\$2:\$B\$16)),\$A\$2:\$A\$16&" "&\$B\$2:\$B\$16,0))
also ARRAY entered
Then copy both down as needed

Replies
4
Views
361
Replies
5
Views
374
Replies
1
Views
450
Replies
3
Views
333
Replies
5
Views
3K

1,137,353
Messages
5,680,995
Members
419,948
Latest member
Sbakker1

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