Determine the year and month from a column and row and lookup sheet to return value?

[anonemous]

I have a master spreadsheet where every month I insert a new column of data and it shifts the rest of the older data/months to the right (instead of going to the end of the sheet). Each month of counts is input into that sheet.
There is a second sheet where I'd like to make a table of the info from above, but in a more compact form.

Of course I could do this and copy and paste manually but I'd like to get excel to read it and return what it looks up.
I thought about using vlookup but I can't think of a way to write it for excel to match the year and month from reading the table correctly.

Bonus side question of a way to determine a running year to date count by adding summing the previous months for a year to date snapshot.

Thank you for reading, I always appreciate the insight gained from knowledge on this forum.

Data sheet:

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1	Types	Oct-21	Sep-21	Aug-21	Jul-21	Jun-21	May-21	Apr-21	Mar-21	Feb-21	Jan-21	Dec-20	Nov-20	Oct-20	Sep-20	Aug-20	Jul-20	Jun-20	May-20	Apr-20	Mar-20	Feb-20	Jan-20	Dec-19	Nov-19	Oct-19	Sep-19	Aug-19	Jul-19	Jun-19	May-19	Apr-19	Mar-19	Feb-19	Jan-19	Dec-18	Nov-18	Oct-18	Sep-18	Aug-18	Jul-18	Jun-18	May-18	Apr-18	Mar-18	Feb-18	Jan-18
2	Oranges	301	288	254	123	190	266	14	158	74	118	67	252	199	214	81	132	304	169	267	101	304	206	157	172	110	198	284	196	151	219	230	19	93	170	215	102	213	38	59	261	295	2	222	269	301	23
3	Apples	312	92	35	286	181	212	188	132	283	86	199	189	124	116	184	266	185	4	178	185	268	19	28	93	303	170	240	302	67	218	107	5	153	133	14	140	103	93	194	303	137	300	30	210	225	73
Sheet1

Tally sheet (the numbers are filled in and the formula should output to these values)

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Apples	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year Total
2	CY 2021	86	283	132	188	212	181	286	35	92	312			1807
3	CY 2020	19	268	185	178	4	185	266	184	116	124	189	199	1917
4	CY 2019	28	93	303	170	240	302	67	218	107	5	153	133	1819
5	YTD 2021	86	369	501	689	901	1082	1368	1403	1495	1807			-
6	YTD 2020	19	287	472	650	654	839	1105	1289	1405	1529	1718	1917	-
7	YTD 2019	28	121	424	594	834	1136	1203	1421	1528	1533	1686	1819	-
8
9
10	Oranges	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year Total
11	CY 2021	118	74	158	14	266	190	123	254	288	301			1786
12	CY 2020	206	304	101	267	169	304	132	81	214	199	252	67	2296
13	CY 2019	170	93	19	230	219	151	196	284	198	110	172	157	1999
14	YTD 2021	118	192	350	364	630	820	943	1197	1485	1786			1786
15	YTD 2020	206	510	611	878	1047	1351	1483	1564	1778	1977	2229	2296	2296
16	YTD 2019	170	263	282	512	731	882	1078	1362	1560	1670	1842	1999	1999
Tally

Cell Formulas
Range	Formula
N2:N4,N11:N13	N2	=SUM(B2:M2)
B14:B16,B5:B7	B5	=B2
C5:K5,C15:M16,C14:K14,C6:M7	C5	=B5+C2
N14:N16	N14	=SUM(B11:M11)

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Apples	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year Total
2	CY 2021	86	283	132	188	212	181	286	35	92	312			1807
3	CY 2020	19	268	185	178	4	185	266	184	116	124	189	199	1917
4	CY 2019	133	153	5	107	218	67	302	240	170	303	93	28	1819
5	YTD 2021	86	369	501	689	901	1082	1368	1403	1495	1807
6	YTD 2020	19	287	472	650	654	839	1105	1289	1405	1529	1718	1917
7	YTD 2019	133	286	291	398	616	683	985	1225	1395	1698	1791	1819
8
9
10	Oranges	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year Total
11	CY 2021	118	74	158	14	266	190	123	254	288	301			1786
12	CY 2020	206	304	101	267	169	304	132	81	214	199	252	67	2296
13	CY 2019	170	93	19	230	219	151	196	284	198	110	172	157	1999
14	YTD 2021	118	192	350	364	630	820	943	1197	1485	1786
15	YTD 2020	206	510	611	878	1047	1351	1483	1564	1778	1977	2229	2296
16	YTD 2019	170	263	282	512	731	882	1078	1362	1560	1670	1842	1999
Tally

Cell Formulas
Range	Formula
B2:M4	B2	=IFERROR(HLOOKUP(DATE(RIGHT($A2,4),MONTH(B$1),1),Sheet1!$1:$10,MATCH($A$1,Sheet1!$A$1:$A$10,0),0),"")
N2:N4,N11:N13	N2	=SUM(B2:M2)
B5:M7,B14:M16	B5	=IF(B2="","",SUM($B2:B2))
B11:M13	B11	=IFERROR(HLOOKUP(DATE(RIGHT($A11,4),MONTH(B$1),1),Sheet1!$1:$10,MATCH($A$10,Sheet1!$A$1:$A$10,0),0),"")

 

[anonemous]

Tested thoroughly and working perfectly. Thank you, @Eric W !

 

[Eric W]

Happy to help! Thanks for the update.