MRP: Materials Planning Model: Formula suggestion needed (Run out Date of Materials)

Good Morning: New User here - Longtime fan of the Excel forum:

In my sample attachment: the following data:
Column B: Inventory Item #
Column H: Quantities (On Hand / On Order / Required by Customer)
Column J: Customer required Delivery Date
Column K: (Existing formula to determine running Cumulative Total Quantity per Inventory Item #)
Column L: Inventory Classifications (A = On Hand, B = On Order, C = Individual Customer Order Requirements/Allocations)
Column M: ** This is where I'd like to enter a formula to determine (for each Item #) - the date on which there will be insufficient inventory to fill the Customer's Order

Methodology:
"A" Inventory Classification is noted as a positive number (Quantity on Hand)
"B" Inventory Classification is noted as a positive number (Quantity on Order)
"C" Inventory Classification could be noted as either a positive, or negative number (Quantity required to fill Orders by Customer)
(if the Quantity associated with "C" Classifications is negative - it suggests a Customer demand for the Item #)
(if the Quantity associated with "C" Classifications is positive - it suggests Inventory was over-issued, and will return to inventory upon job completion)

Attachment (Excel sample.jpg):
Displays three (3) possibilities (Item #'s 0324, 0665 and K1466)

Example 0324:
Cumulative Quantity on Hand + Quantity on Order - Customer Requirement Quantities "runs out" (goes negative) on the date of 03/20/2019: This is the date to return in the formula.

Example 0665 (tricky one):
Cumulative Quantity on Hand + Quantity on Order - Customer Requirement Quantities "runs out" (goes negative) on the date of 12/17/2018: however, there is inventory returned (145.83) on 12/27/2018, which is enough to satisfy the order for 01/18/2019. Then, the requirement of <58.500> on the Order for 02/28/2019 "runs out" the available inventory. This is the date to return in the formula. 02/28/2019 In other words, The run out date needed for Column "M" is the last time the cumulative inventory runs into the negative.

Example K1466:
Cumulative Quantity on Hand + Quantity on Order - Customer Requirement Quantities never "runs out" (goes negative). A "text" return of "OK" would be sufficient, as there is no current run-out date.

I'd like to find a formula solution which would return one specific run-out date (or the text "OK" for each change in Item # (Column "B")

Thank you for the opportunity to share this. Dave

Glenn:

Thank you for your prompt response. I've attached a small Excel file snippet of data (instead of the picture).

My overall file will have rows in the 3000-5000 range at present, with approximately 1-500 unique Item Numbers.

I've noted the desired solution (now in Column "F") - as when I pasted, my original had hidden rows.
(The column identifiers in the current Excel attachment are slightly different from those documented in my original post).

Dave

Bo_Ry: You're input is much appreciated. I'm testing on my sample Excel and results seem promising.

As I'm trying to adapt to my master Excel, I have a question:

In your suggestion, can you clarify the 2 right after the (LOOKUP? - was that meant to specify a particular column? Or, reference from the starting Column "F"?

=IFERROR(IF(LOOKUP(2,1/(($A$2:$A$99=A2)*$D$2:$D$99<0)/($D$1:$D$21>=0),$C$2:$C$99)=C2,C2,""),IF(A2<>A1,"OK",""))

I feel progress on the way... thank you. Dave

Bo:

Further clarification:

In my Excel Master file: My columns are as noted below:

Column B: Inventory Item #
Column H: Quantities (On Hand / On Order / Required by Customer)
Column J: Customer required Delivery Date
Column K: (Existing formula to determine running Cumulative Total Quantity per Inventory Item #)
Column L: Inventory Classifications (A = On Hand, B = On Order, C = Individual Customer Order Requirements/Allocations)
Column M: ** This is where I'd like to enter a formula to determine (for each Item #) - the date on which there will be insufficient inventory to fill the Customer's Order

I hope this does not confuse the issue. Dave

Bo:

Thank you. I will be reviewing shortly, and running a final test in the morning.

Much appreciated,

Dave

Bo:

I really do appreciate your time and effort.

As you had originally surmised, the formula ceases to work as desired past row 100 or so.

At present, the row count is in the 2500-3000 range. Do you think a tweak to the proposed formula would work?

Or, perhaps a slightly different approach?

Happy New Year,

Dave

Bo!

This is Brilliant :-) Works like a charm.

Thank you for your thoughtful insight and expertise.

Happy New Year,

Dave

Bo:

So Good!

I've made a slight modification (noted in bold below:

=IF(B2=B1,"",IFERROR(LOOKUP(2,1/(($B$2:$B$9999=B2)*$K$2:$K$9999<0)/($K$1:$K$9998>=0),$J$2:$J$9999),"OK"))

This seems to work beautifully.

Thank you for your assistance.

Dave