VLOOKUP to Find the MIN (that is not zero)

[Huber57]

All:

I am stumped on this.

I have combed through the archives and cannot find the answer

I need to return the minimum price for an item (where the price is not zero)

Item Price Vendor
Apple $1 Wal-Mart
Apple $2 Target
Apple Sears

I need the formula to return $1.

=IF(MIN(VLOOKUP("Apple",B2:B4,FALSE)))

is definitely not working.

Thanks,

Doug

 

[T. Valko]

Try this array formula** :

=MIN(IF((A2:A4="apple")*(B2:B4<>""),B2:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[cm]

I would use the DMIN function.

your 'data base' would be the following (assuming column 3 not relevant) in
cells F11: g14

item count
apple 0
apple 1
apple 2

your 'criteria' would be in I11:J12:
count item

0 apple

your function is =DMIN(F11:G14,"count",I11:J12), which returns the number 1.

 

[Eduardo]

Hi,
try

=IF(MIN(B1:B3)<>0,VLOOKUP("Apple",A1:C3,3,FALSE))

change range to fit your needs

 

[Huber57]

T.,

That is perfect. Thanks much!

Doug

Try this array formula** :

=MIN(IF((A2:A4="apple")*(B2:B4<>""),B2:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome. Thanks for the feedback!