PDA

View Full Version : Lookup multiple values and compare different scenarios to get a specific result



mws
04-12-2014, 12:39 AM
In the attached sheet I want formula in column C that will give the new order quantity with following logic;

If ("Product"="Product")of column A & I, ("Closing Stock" of a particular product<"ROL" of that product) & (Current "Date" (say date in B5 if formula is being applied in C5)-Any latest previous "New Order" Date>="Lead Time" of that product)

Means if it has given new order quantity once then it should not give it again within the lead time even though the closing stock is less than the ROL. For example if it gives 150 in C3 then 150 should come on or after 21.03.2014 if closing stock is less than ROL i.e; 50.

Our formula should perform the 3rd logic only if it finds any new order qty in Array C otherwise first 2 logic are enough.

Thanks in anticipation.

Bob Phillips
04-12-2014, 05:14 AM
Give us some worked example, your explanation lost me.

mws
04-12-2014, 06:10 AM
The formula in the attached sheet is almost OK with a littler problem.
I have to manually mention new order quantities in first dates, it should be auto. further if I remove 100 from C2 then it should give 100 in C5 coz closing stock<ROL in C5 and also there is no previous order within the last 40 days but it doesn't.

Jacob Hilderbrand
04-12-2014, 09:01 AM
cross post: http://www.msofficeforums.com/excel/20565-lookup-multiple-values-compare-different-scenarios-get.html

If you are posting on multiple forums provide a link in the post so people are not wasting time responding to something that may already have been answered elsewhere.

Thanks

mws
05-24-2014, 05:33 AM
The problem worked out finally.
However thanks for all the support.