Excel formulas/function help

[Jade_Goard]

Hi, I am trying to do an analysis to determine what is the best order qty and pricing considering inventory carrying cost. I put together an example but my file has  more than 200 lines.

This table has my source data and calculation to determine what is the best order qty and pricing.

Take PN 1 for example, it is best for me to order at 300 units at pricing of 0.6.

PN	Order Qty	Price	Best Order Qty
PN1	100	0.5	Yes
PN1	200	0.6	Yes
PN1	300	0.7	No
PN2	150	0.1	Yes
PN2	250	0.2	Yes
PN2	350	0.3	No
PN2	450	0.4	No
PN3	50	1.5	Yes
PN3	100	1.6	No
PN3	150	1.7	No
PN3	200	1.8	No

I would like to know how to write a formulate using above input to spit out a table below. 

PN	Best Order Qty	Price
PN1	200	0.6
PN2	250	0.2
PN3	50	1.5

Thank you for any help/tip!

[#2]

@Jade_Goard 

Check this ,, has formula in cell D3:

=IF(AND(B3=$F$2,C3>=$F$3),"Yes","No")

An array (CSE) formula in cell H3:

{=IFERROR(INDEX(A$3:A$13, SMALL(IF($D$3:$D$13="Yes", ROW($D$3:$D$13)-ROW($D$3)+1), ROW(1:1))),"" )}

• Finish formula with Ctrl+Shift+Enter, and fill across the range.
• Adjust cell references in the formula as needed,

[#3]

@Rajesh_Sinha thank you very much.

does this mean to calculate values in D (best order qty), I need to adjust F2 and F3, as each PN have different pricing tiers?

That is something I hope to shy away as my actual file has maybe 100-200 unique PNs and each PN has 3-6 price tiers. 

[#4]

@Jade_Goard 

Simplest way is to use Power Query if you consider such option - filter on Best Order; sort by PN (ascending) and Price (descending), remove duplicates.