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

kumquattie

New Member
Joined
Sep 18, 2017
Messages
1
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:

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

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


I want to be able to populate this table:

ItemLowest CostAssociated 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Based in your data table being in A:C...
A​
B​
C​
21​
ItemLowest CostAssociated Vendor
22​
Soccer ball1.9Five Below
23​
Football1.75Modell's
24​
Basketball3.75Walmart
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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?

Disable AdBlock

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
Back
Top