Help choosing minimum price if in stock

Hi

Try this

=MIN(IF(C2:C7="y";E2:E7))

CSE= Array Formula.

Try this formula in E2

=MIN(IF('input sheet'!C2:G2>0,'input sheet'!H2:L2))

That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER

copy down column

Take a look to the example..

That won't return correct value if two suppliers has same price but first is out of stock.

Here, look at this:

For other part use DDL formula, or some INDEX approach.

Hi,

As an array formula (entered with Ctrl-Shift-Enter) in E2 and copied down.
=MIN(IF('input sheet'!C2:G2>0,'input sheet'!H2:L2))

I think I already suggested that one Richard.....

I think you need that formula in E2 for lowest in stock price, then you can use this formula in D2 for the supplier

=IF(C2="n","",INDEX('input sheet'!H$1:L$1,MATCH(E2,'input sheet'!H2:L2,0)))

see attached

That won't return you correct value since cheapest supplier may be out of stock.
Check mine example.

@ddl

Indeed you did - sorry, I missed that.

Regards

Are you referring to my last suggestion?

zbor is right, mine gives correct results for that example but won't necessarily always work, I amended D2 to this formula copied down

=IF(C2="n","",INDEX('input sheet'!H$1:L$1,MATCH(1,INDEX(('input sheet'!H2:L2=E2)*('input sheet'!C2:G2>0),0),0)))

see attached

Originally Posted by zbor

That won't return you correct value since cheapest supplier may be out of stock.
Check mine example.

Nicely spotted

Thanks zbor, I agree that my first D2 formula isn't correct (since corrected, see last post). I think that it will work in most cases, even if the cheaper supplier is out of stock because it's referencing the minimum in stock price......but if there is a tie between the cheapest prices and some of those suppliers are out of stock then it might pick the wrong supplier.

At the end nice non-array solution from all of us