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

[kumquattie]

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:

[TABLE="class: grid, width: 354"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Cost[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD]$2.00[/TD]
[TD]Dicks[/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD]$3.50[/TD]
[TD]Sports Authority[/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD]$2.80[/TD]
[TD]Modell's [/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD]$3.00[/TD]
[TD]Jane's Sporting goods[/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD]$1.90[/TD]
[TD]Five Below[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]$1.75[/TD]
[TD]Modell's [/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]$2.15[/TD]
[TD]Dicks[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]$2.80[/TD]
[TD]Jane's Sporting goods[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]$1.90[/TD]
[TD]Five Below[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$4.50[/TD]
[TD]Jane's Sporting goods[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$4.00[/TD]
[TD]Five Below[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$6.00[/TD]
[TD]Sports Authority[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$3.75[/TD]
[TD]Walmart[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$5.25[/TD]
[TD]Dicks[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]$5.00[/TD]
[TD]Modell's [/TD]
[/TR]
</tbody>[/TABLE]


I want to be able to populate this table:

[TABLE="class: grid, width: 353"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Lowest Cost[/TD]
[TD]Associated Vendor[/TD]
[/TR]
[TR]
[TD]Soccer ball[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


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!

 

[FDibbins]

Based in your data table being in A:C...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

21​

[/td][td]Item[/td][td]Lowest Cost[/td][td]Associated Vendor[/td][/tr]

[tr][td]

22​

[/td][td]Soccer ball[/td][td]1.9[/td][td]Five Below[/td][/tr]

[tr][td]

23​

[/td][td]Football[/td][td]1.75[/td][td]Modell's[/td][/tr]

[tr][td]

24​

[/td][td]Basketball[/td][td]3.75[/td][td]Walmart[/td][/tr]
[/table]
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