SUM, INDEX, MATCH, etc.

[Unlucky]

I'm trying to pull data from a table into another table, based on date. I can't figure out why the formula doesn't work. I've tried SUM, SUMIF, SUMIFS, SUMPRODUCT, INDEX, INDEX & MATCH, LOOKUP, and VLOOKUP. I either get #value, #na or a zero. I tried for 4 hours and can't get it! The second table is only the current 12 month period pulling from the raw data over a 36 month period. I've tried it with and without an array and its the same resullt.

Source table:

DCA_BAE_(06085)_PROGRAM_BRIEF_2023-02-15.xlsx
	AQ	AR	AS	AT	AU	AV	AW	AX	AY
4	PROGRAM	DATE	PART	TOP	BOTTOM	INSIDE	OUTSIDE	TOTAL	DPU
5	ABC	Jan-22	5	10	0	26	73	109.00	21.80
6	ABC	Feb-22	5	9	0	46	40	95.00	19.00
7	ABC	Mar-22	5	11	0	1	0	12.00	2.40
8	ABC	Apr-22	5	20	0	68	20	108.00	21.60
9	ABC	May-22	10	16	0	173	429	618.00	61.80
10	ABC	Jun-22	10	10	0	149	249	408.00	40.80
11	ABC	Jul-22	4	0	0	77	130	207.00	51.75
12	ABC	Aug-22	5	12	0	65	128	205.00	41.00
13	ABC	Sep-22	8	13	0	112	128	253.00	31.63
14	ABC	Oct-22	8	1	0	98	175	274.00	34.25
15	ABC	Nov-22	12	11	0	27	43	81.00	6.75
16	ABC	Dec-22	24	0	0	52	123	175.00	7.29
17	ABC	Jan-23	14	7	0	50	104	161.00	11.50
18	ABC	Feb-23	12	5	6	19	0	30.00	2.50
19	ABC	Mar-23	0	0	0	0	0	0.00	0.00
20	ABC	Apr-23	0	0	0	0	0	0.00	0.00
21	ABC	May-23	0	0	0	0	0	0.00	0.00
22	ABC	Jun-23	0	0	0	0	0	0.00	0.00
23	ABC	Jul-23	0	0	0	0	0	0.00	0.00
24	ABC	Aug-23	0	0	0	0	0	0.00	0.00
25	ABC	Sep-23	0	0	0	0	0	0.00	0.00
26	ABC	Oct-23	0	0	0	0	0	0.00	0.00
27	ABC	Nov-23	0	0	0	0	0	0.00	0.00
28	ABC	Dec-23	0	0	0	0	0	0.00	0.00
29	ABC	Jan-24	0	0	0	0	0	0.00	0.00
30	ABC	Feb-24	0	0	0	0	0	0.00	0.00
31	ABC	Mar-24	0	0	0	0	0	0.00	0.00
32	ABC	Apr-24	0	0	0	0	0	0.00	0.00
33	ABC	May-24	0	0	0	0	0	0.00	0.00
34	ABC	Jun-24	0	0	0	0	0	0.00	0.00
35	ABC	Jul-24	0	0	0	0	0	0.00	0.00
36	ABC	Aug-24	0	0	0	0	0	0.00	0.00
37	ABC	Sep-24	0	0	0	0	0	0.00	0.00
38	ABC	Oct-24	0	0	0	0	0	0.00	0.00
39	ABC	Nov-24	0	0	0	0	0	0.00	0.00
40	ABC	Dec-24	0	0	0	0	0	0.00	0.00
ACV

Cell Formulas
Range	Formula
AS5	AS5	=$F$6
AT5	AT5	=F$7
AU5	AU5	=F$8
AV5	AV5	=F$9
AW5	AW5	=F$10
AX5:AX40	AX5	=SUM($AT5:$AW5)
AY5:AY40	AY5	=IFERROR($AX5/$AS5,0)
AS6	AS6	=$G$6
AT6	AT6	=$G$7
AU6	AU6	=$G$8
AV6	AV6	=$G$9
AW6	AW6	=$G$10
AS7	AS7	=$H$6
AT7	AT7	=$H$7
AU7	AU7	=$H$8
AV7	AV7	=$H$9
AW7	AW7	=$H$910
AS8	AS8	=$I$6
AT8	AT8	=$I$7
AU8	AU8	=$I$8
AV8	AV8	=$I$9
AW8	AW8	=$I$10
AS9	AS9	=$J$6
AT9	AT9	=$J$7
AU9	AU9	=$J$8
AV9	AV9	=$J$9
AW9	AW9	=$J$10
AS10	AS10	=$K$6
AT10	AT10	=$K$7
AU10	AU10	=$K$8
AV10	AV10	=$K$9
AW10	AW10	=$K$10
AS11	AS11	=$L$6
AT11	AT11	=$L$7
AU11	AU11	=$L$8
AV11	AV11	=$L$9
AW11	AW11	=$L$10
AS12	AS12	=$M$6
AT12	AT12	=$M$7
AU12	AU12	=$M$8
AV12	AV12	=$M$9
AW12	AW12	=$M$10
AS13	AS13	=$N$6
AT13	AT13	=$N$7
AU13	AU13	=$N$8
AV13	AV13	=$N$9
AW13	AW13	=$N$10
AS14	AS14	=$O$6
AT14	AT14	=$O$7
AU14	AU14	=$O$8
AV14	AV14	=$O$9
AW14	AW14	=$O$10
AS15	AS15	=$P$6
AT15	AT15	=$P$7
AU15	AU15	=$P$8
AV15	AV15	=$P$9
AW15	AW15	=$P$10
AS16	AS16	=$Q$6
AT16	AT16	=$Q$7
AU16	AU16	=$Q$8
AV16	AV16	=$Q$9
AW16	AW16	=$Q$10
AS17	AS17	=$R$6
AT17	AT17	=$R$7
AU17	AU17	=$R$8
AV17	AV17	=$R$9
AW17	AW17	=$R$10
AS18	AS18	=$S$6
AT18	AT18	=$S$7
AU18	AU18	=$S$8
AV18	AV18	=$S$9
AW18	AW18	=$S$10
AS19	AS19	=$T$6
AT19	AT19	=$T$7
AU19	AU19	=$T$8
AV19	AV19	=$T$9
AW19	AW19	=$T$10
AS20	AS20	=$U$6
AT20	AT20	=$U$7
AU20	AU20	=$U$8
AV20	AV20	=$U$9
AW20	AW20	=$U$10
AS21	AS21	=$V$6
AT21	AT21	=$V$7
AU21	AU21	=$V$8
AV21	AV21	=$V$9
AW21	AW21	=$V$10
AS22	AS22	=$W$6
AT22	AT22	=$W$7
AU22	AU22	=$W$8
AV22	AV22	=$W$9
AW22	AW22	=$W$10
AS23	AS23	=$X$6
AT23	AT23	=$X$7
AU23	AU23	=$X$8
AV23	AV23	=$X$9
AW23	AW23	=$X$10
AS24	AS24	=$Y$6
AT24	AT24	=$Y$7
AU24	AU24	=$Y$8
AV24	AV24	=$Y$9
AW24	AW24	=$Y$10
AS25	AS25	=$Z$6
AT25	AT25	=$Z$7
AU25	AU25	=$Z$8
AV25	AV25	=$Z$9
AW25	AW25	=$Z$10
AS26	AS26	=$AA$6
AT26	AT26	=$AA$7
AU26	AU26	=$AA$8
AV26	AV26	=$AA$9
AW26	AW26	=$AA$10
AS27	AS27	=$AB$6
AT27	AT27	=$AB$7
AU27	AU27	=$AB$8
AV27	AV27	=$AB$9
AW27	AW27	=$AB$10
AS28	AS28	=$AC$6
AT28	AT28	=$AC$7
AU28	AU28	=$AC$8
AV28	AV28	=$AC$9
AW28	AW28	=$AC$10
AS29	AS29	=$AD$6
AT29	AT29	=$AD$7
AU29	AU29	=$AD$8
AV29	AV29	=$AD$9
AW29	AW29	=$AD$10
AS30	AS30	=$AE$6
AT30	AT30	=$AE$7
AU30	AU30	=$AE$8
AV30	AV30	=$AE$9
AW30	AW30	=$AE$10
AS31	AS31	=$AF$6
AT31	AT31	=$AF$7
AU31	AU31	=$AF$8
AV31	AV31	=$AF$9
AW31	AW31	=$AF$10
AS32	AS32	=$AG$6
AT32	AT32	=$AG$7
AU32	AU32	=$AG$8
AV32	AV32	=$AG$9
AW32	AW32	=$AG$10
AS33	AS33	=$AH$6
AT33	AT33	=$AH$7
AU33	AU33	=$AH$8
AV33	AV33	=$AH$9
AW33	AW33	=$AH$10
AS34	AS34	=$AI$6
AT34	AT34	=$AI$7
AU34	AU34	=$AI$8
AV34	AV34	=$AI$9
AW34	AW34	=$AI$10
AS35	AS35	=$AJ$6
AT35	AT35	=$AJ$7
AU35	AU35	=$AJ$8
AV35	AV35	=$AJ$9
AW35	AW35	=$AJ$10
AS36	AS36	=$AK$6
AT36	AT36	=$AK$7
AU36	AU36	=$AK$8
AV36	AV36	=$AK$9
AW36	AW36	=$AK$10
AS37	AS37	=$AL$6
AT37	AT37	=$AL$7
AU37	AU37	=$AL$8
AV37	AV37	=$AL$9
AW37	AW37	=$AL$10
AS38	AS38	=$AM$6
AT38	AT38	=$AM$7
AU38	AU38	=$AM$8
AV38	AV38	=$AM$9
AW38	AW38	=$AM$10
AS39	AS39	=$AN$6
AT39	AT39	=$AN$7
AU39	AU39	=$AN$8
AV39	AV39	=$AN$9
AW39	AW39	=$AN$10
AS40	AS40	=$AO$6
AT40	AT40	=$AO$7
AU40	AU40	=$AO$8
AV40	AV40	=$AO$9
AW40	AW40	=$AO$10
AQ5:AQ40	AQ5	=$H$2

Reporting Table (used to make charts):

DCA_BAE_(06085)_PROGRAM_BRIEF_2023-02-15.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
17	Program Quality Status Totals				Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
18	Inspected:				1668110	5	5	5	5	5	5	5	5	5	5	5
19	Top:
20	Bottom:
21	Inside:
22	Outside:
23	Total Defects:
24	DPU:
25	FPY:
ACV

Cell Formulas
Range	Formula
F17	F17	=$BB$5
G17	G17	=$BB$6
H17	H17	=$BB$7
I17	I17	=$BB$8
J17	J17	=$BB$9
K17	K17	=$BB$10
L17	L17	=$BB$11
M17	M17	=$BB$12
N17	N17	=$BB$13
O17	O17	=$BB$14
P17	P17	=$BB$15
Q17	Q17	=$BB$16
F18	F18	=SUM(F$17,$AR$5:$AR$40,$AS$5:$AS$40)
G18:Q18	G18	=LOOKUP($AR$5:$AR$40,G$17,$AS$5:$AS$40)
B18:B22	B18	=B6
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Unlucky]

This is the rolling 12 month table:

DCA_BAE_(06085)_PROGRAM_BRIEF_2023-02-15.xlsx
	BA	BB	BC	BD
4	MONTH	MONTH	MONTH	MONTH
5	-12	Feb-22	Mar-22	1
6	-11	Mar-22	Apr-22	1
7	-10	Apr-22	May-22	1
8	-9	May-22	Jun-22	1
9	-8	Jun-22	Jul-22	1
10	-7	Jul-22	Aug-22	1
11	-6	Aug-22	Sep-22	1
12	-5	Sep-22	Oct-22	1
13	-4	Oct-22	Nov-22	1
14	-3	Nov-22	Dec-22	1
15	-2	Dec-22	Jan-23	1
16	-1	Jan-23	Feb-23	1
ACV

Cell Formulas
Range	Formula
BB5:BB16	BB5	=EDATE(TODAY(),$BA5)
BC5:BC16	BC5	=EDATE(BB5,BD5)