Nested If Statement + Index/Match with multiple criteria

[lpost1214]

I am trying to populate an open order report with next production dates since our system does not peg production to orders. I am trying write a formula that will meet multiple criteria, but can't seem to get the formula to work just right. Below is a sample of the data and below that the production data and the formula I've written.

Formula:

IFERROR(IF(OR(VALUE(g4) > 0,A4=" "),"",IF(AND(VALUE(g4)=0,a3="Description"),VLOOKUP(B4,Sched!A:C,3,0),IF(B4=" ","",IF(AND(VALUE(g4)=0,VALUE(g3) <> 0),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)),IF(AND(VALUE(g4)=0,VALUE(g3)=0,VLOOKUP(B4,Sched!A:C,3,0)-h3 < h4),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)+1),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0))))))),"-")

<tbody>
</tbody><colgroup><col span="38"></colgroup>

Criteria:
*If value in balance on hand (column G) = 0, and first row for item (column A) then look up first production quantity for that item.
*If value in balance on hand (column G) = 0, and not first row for item (column A), check to see if production quantity looked up in row above minus the open order qty for the line above, >= open order quantity in the current row. If true, then return the same production quantity as above, if false, return next production quantity for that item.
*If there is no look up value once criteria is met, return a 0
*If an empty row or the value in column G is >0, then show as blank

Thanks in advance for your help!

Description

Cust PO #

Del Date

Customer PO Quantity

Shipped Qty

Open Order Qty

Balance On Hand

Next Production Qty

<colgroup><col width="180" style="width: 135pt; mso-width-source: userset; mso-width-alt: 6582;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <tbody> </tbody>

AS Ultra Trop HBRC 8/3/50	2171337828	4/20/2018	5417	0	5417	0	5472
AS Ultra Trop HBRC 8/3/50	2171369379	6/4/2018	5417	0	5417	0	5472
							-
AS Trop HBG RC 8/3/82ct	2171328949	4/9/2018	4750	0	4750	48
AS Trop HBG RC 8/3/82ct	2171369382	6/11/2018	1425	0	1425	0	1440
							-
Citracal GEL 12/2/70ct	2171264847	7/6/2018	2500	0	2500	0	1
Citracal GEL 12/2/70ct	2171307716	8/10/2018	1250	0	1250	0	6788
							-
AS HB GEL 8/3/36ct	2171259218	2/9/2018	4106	0	4106	3883
AS HB GEL 8/3/36ct	2171307607	3/12/2018	2542	0	2542	1341
AS HB GEL 8/3/36ct	2171332469	4/11/2018	2542	0	2542	0	11400
							-
AS HB GEL 8/3/60ct	2171259219	2/9/2018	2933	0	2933	0	2934
AS HB GEL 8/3/60ct	2171307606	3/9/2018	1467	0	1467	0	1
							-
AS HB PM GEL 8/3/46ct	2171348593	3/26/2018	3750	0	3750	3838
AS HB PM GEL 8/3/46ct	2171348594	4/26/2018	3750	0	3750	88
AS HB PM GEL 8/3/46ct	2171352728	5/11/2018	3750	0	3750	0	3750
AS HB PM GEL 8/3/46ct	2171375520	6/15/2018	3750	0	3750	0	3750
							-
AS HB PM GEL 8/3/24ct	2171366788	6/1/2018	2083	0	2083	0	3126
AS HB PM GEL 8/3/24ct	2171375519	6/15/2018	1042	0	1042	0	3126
							-
AS Trop HBG RC 8/3/68ct	2171352178	5/24/2018	4167	0	4167	0	4210
							-
AS HB RC Frt 8/3/75ct	2171362181	5/24/2018	4167	0	4167	0	4210

<colgroup><col width="180" style="width: 135pt; mso-width-source: userset; mso-width-alt: 6582;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <tbody> </tbody>

Part Description	Due Date	Production Quantity
AS HB GEL 8/3/36ct	5/11/2018	11400
AS HB GEL 8/3/36ct	6/1/2018	1
AS HB GEL 8/3/60ct	5/18/2018	2934
AS HB GEL 8/3/60ct	6/1/2018	1
AS HB PM GEL 8/3/24ct	5/4/2018	3126
AS HB PM GEL 8/3/46ct	4/20/2018	3750
AS HB PM GEL 8/3/46ct	5/18/2018	3750
AS HB PM GEL 8/3/46ct	6/7/2018	6250
AS HB RC Frt 8/3/75ct	5/11/2018	4210
AS Trop HBG RC 8/3/68ct	4/6/2018	4210
AS Trop HBG RC 8/3/82ct	5/4/2018	1440
AS Trop HBG RC 8/3/82ct	5/25/2018	1920
AS Trop HBG RC 8/3/82ct	6/15/2018	1440
AS Ultra Trop HBRC 8/3/50	4/6/2018	5472
AS Ultra Trop HBRC 8/3/50	5/4/2018	5472
Citracal GEL 12/2/70ct	6/8/2018	1

<colgroup><col width="209" style="width: 157pt; mso-width-source: userset; mso-width-alt: 7643;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="162" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5924;"> <tbody> </tbody>