Return Value to Different Line with Possible Duplicate Information

Question

In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.

See link below. Here is an explanation of what I have so far in the spreadsheet:

1. Raw data that I need to modify is in columns A:D.
2. I have an ArrayFormula in E2 that pulls rows from A:D where C is not blank.
3. Where C is blank, I need D to be inserted into I where E:F match A:C. <---This is what I can't figure out.

I'm not married to the formula in E2 if there is a more-efficient way to grab all of the information in one shot. Here's a sample of the data, and what I have so far.

I highlighted groups to show that there are sometimes duplicates or triplicates of the Zone. The value of D should go in I in line with the first iteration of the matching Route/Zone.

https://docs.google.com/spreadsheets/d/1xIum-fNKfdS4XWtpaHHJpluTUA3kUEDDMzStQsSxyi4/edit?usp=sharing

Here is the first 50 rows of what I have so far expressed as a CSV. Rows A:D are raw data and rows E:H are from this formula:

=ArrayFormula(SORT(ArrayFormula(IF(C2:C<>"",A2:D,IFERROR({A2:B,D1:D},""))),1,TRUE))

A	B	C	D	E	F	G	H	I
Route	Zone	Tote	Packages	Route	Zone	Tote	In Tote	NOT in Tote
102	J-18.1B	Green 038	20	102	J-18.1B	Green 038	20
102	J-18.1B		1	102	J-18.4A	Orange 941	14
102	J-18.4A	Orange 941	14	102	J-18.4A	Green 362	14
102	J-18.4A	Green 362	14	102	J-18.3A	Black 906	13
102	J-18.4A		1	102	J-18.4C	Navy 891	27
102	J-18.3A	Black 906	13	102	J-18.1D	Orange 288	16
102	J-18.3A		1	102	J-18.2D	Navy 952	19
102	J-18.4C	Navy 891	27	102	J-18.3D	Black 909	14
102	J-18.4C		1	102	J-18.2B	Green 577	31
102	J-18.1D	Orange 288	16	102	J-18.3B	Navy 528	17
102	J-18.1D		1	102	J-18.4B	Yellow 405	12
102	J-18.2D	Navy 952	19	102	J-18.4B	Orange 920	12
102	J-18.2D		1	102	J-18.1C	Green 272	19
102	J-18.3D	Black 909	14	102	J-18.2C	Orange 425	18
102	J-18.2B	Green 577	31	102	J-18.3C	Black 978	9
102	J-18.2B		3	103	H-18.2A	Green 315	25
102	J-18.3B	Navy 528	17	103	H-18.3A	Orange 109	4
102	J-18.3B		3	103	H-18.4A	Green 382	12
102	J-18.4B	Yellow 405	12	103	H-18.1B	Navy 662	10
102	J-18.4B	Orange 920	12	103	H-18.4B	Black 551	17
102	J-18.4B		4	103	H-18.3B	Orange 334	28
102	J-18.1C	Green 272	19	103	H-18.2B	Green 456	25
102	J-18.1C		2	103	J-18.4D	Black 741	14
102	J-18.2C	Orange 425	18	103	J-18.1E	Yellow 536	18
102	J-18.2C		1	103	J-18.2E	Navy 771	17
102	J-18.3C	Black 978	9	103	J-18.3E	Yellow 838	23
102	J-18.3C		1	103	J-18.4E	Orange 970	19
103	H-18.2A	Green 315	25	103	J-18.3G	Navy 298	15
103	H-18.2A		2	103	J-18.4G	Black 688	19
103	H-18.3A	Orange 109	4	103	H-18.1A	Navy 170	13
103	H-18.3A		1	103	J-18.2G	Orange 681	15
103	H-18.4A	Green 382	12	103	J-18.1G	Yellow 508	32
103	H-18.4A		1	104	H-18.2C	Yellow 207	17
103	H-18.1B	Navy 662	10	104	H-18.3C	Navy 073	16
103	H-18.1B		1	104	H-18.1C	Yellow 572	12
103	H-18.4B	Black 551	17	104	H-18.4C	Yellow 424	12
103	H-18.3B	Orange 334	28	104	H-18.1D	Green 087	17
103	H-18.3B		1	104	H-18.2D	Orange 803	18
103	H-18.2B	Green 456	25	104	H-18.3D	Black 859	23
103	H-18.2B		1	104	H-18.4D	Navy 525	30
103	J-18.4D	Black 741	14	104	H-18.1E	Yellow 227	22
103	J-18.4D		1	104	H-18.2E	Black 021	22
103	J-18.1E	Yellow 536	18	104	H-18.3E	Navy 754	17
103	J-18.1E		4	104	H-18.4E	Orange 925	15
103	J-18.2E	Navy 771	17	104	H-18.1G	Black 575	8
103	J-18.3E	Yellow 838	23	104	H-18.2G	Navy 087	12
103	J-18.3E		2	104	H-18.3G	Orange 747	16
103	J-18.4E	Orange 970	19	105	H-18.4G	Black 928	25
103	J-18.4E		1	105	G-18.1A	Green 504	17

Answer 1

In cell I4, use query() to get the report, and vlookup() with a compound key to put the results in the correct rows, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      E4:E & F4:F, 
      query( 
        { A4:D, A4:A & B4:B }, 
        "select Col5, sum(Col4) 
         where Col3 is null 
         group by Col5 
         label sum(Col4) '' ", 0 
      ), 
      2, false 
    ) 
  ) 
)

To remove results where there are priors, use countifs() in cell J4, like this:

=map( 
  E4:E, F4:F, I4:I, 
  lambda( 
    route, zone, notIn, 
    if( 
      1 = countifs(E4:route, route, F4:zone, zone), 
      notIn, 
      iferror(1/0) 
    ) 
  ) 
)