Min of non-consecutive range with criteria

[bloppers]

Hi Guys,

I've tried searching for a solution but couldn't come up with anything that was suitable.

Basically, I want to try to find the min price of each item bid by each vendor. This I can do easily using excel formulas but the problem comes up when I want to find the min price of only vendors on the vendor list.

Small snapshot:

[TABLE="width: 654"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]AVL[/TD]
[TD]non AVL[/TD]
[TD][/TD]
[TD]Bidder A[/TD]
[TD][/TD]
[TD]Bidder B[/TD]
[TD][/TD]
[TD]Bidder C[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Quantity[/TD]
[TD]Min Price[/TD]
[TD]Min Price[/TD]
[TD]Unit Price[/TD]
[TD]Total Price[/TD]
[TD]Unit Price[/TD]
[TD]Total Price[/TD]
[TD]Unit Price[/TD]
[TD]Total Price[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]AVL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


I'm not sure what's the best way to go about getting what I need (formula/VBA).

Please advice!

Thanks!

 

[Aladin Akyurek]

What is then the formula you use?

 

[bloppers]

What is then the formula you use?

I only got the first part without criteria.

=IFERROR(SMALL(item1,1+FREQUENCY(item1,0)),"N/A")

It lets me select the lowest bidder excluding 0.

 

[Aladin Akyurek]

I only got the first part without criteria.

=IFERROR(SMALL(item1,1+FREQUENCY(item1,0)),"N/A")

It lets me select the lowest bidder excluding 0.

Control+shift+enter, not just enter:

=IFERROR(SMALL(IF(ISNUMBER(MATCH(Vendors,VendorList,0)),Item1),INDEX(FREQUENCY(IF(ISNUMBER(MATCH(Vendors,VendorList,0)),Item1),0),1)+1),"N/A")

where Vendors is the range corresponding to Item1 and VendorList is a range housing the vendors of interest.