Lookup with Date

[JTL9161]

I have dates in column A (Worksheet2021) with total in Col B and Average in Col C. I am trying to refererence each Week's average versus each Week from this year. I will have an Week1 Average for 2022 in Col A on another (Worksheet2022) and want to pull the coorisponding Average from 2021 and want the Week1 average from the 2021 worksheet

Worksheet2021
Dates # Avg.
04/26/21 79 79.00 Week1
05/10/21 71 75.00 Week3
05/24/21 69 73.00 Week5

04/19/21 70 70.00 Week2
05/03/21 72 71.00 Week4
05/17/21 79 73.67 Week6

End Result
Worksheet2022
2022 Avg 2021 Avg
04/27/22 77 77.00 79.00 Week1
05/11/22 70 73.50 75.00 Week3
05/25/22 74 73.66 73.00 Week5

04/20/22 69 69.00 70.00 Week2
05/04/22 73 71.00 71.00 Week4
05/18/22 74 72.00 73.67 Week6

Need the Lookup(?) formula on Worksheet2022 that will take the appropriate Average from the 2021 worksheet to the 2022 worksheet.

Thank you,
James

 

[ExceLoki]

this works using the week names as the common factor to compare to. let me know if this works for you.
---------------
2021

Book1
	A	B	C	D
1	Dates	#	Avg.
2	4/26/2021	79	79.00	Week1
3	5/10/2021	71	75.00	Week3
4	5/24/2021	69	73.00	Week5
5
6	4/19/2021	70	70.00	Week2
7	5/3/2021	72	71.00	Week4
8	5/17/2021	79	73.67	Week6
2021

Cell Formulas
Range	Formula
C2,C6	C2	=AVERAGE(B2)
C3,C7	C3	=AVERAGE(B2:B3)
C4,C8	C4	=AVERAGE(B2:B4)

---------------
2022

Book1
	A	B	C	D	E
1	Dates	#	2022 Avg	2021 Avg
2	4/27/2022	77	77.00	79.00	Week1
3	5/11/2022	70	73.50	75.00	Week3
4	5/25/2022	74	73.67	73.00	Week5
5
6	4/20/2022	69	69.00	70.00	Week2
7	5/4/2022	73	71.00	71.00	Week4
8	5/18/2022	74	72.00	73.67	Week6
2022

Cell Formulas
Range	Formula
C2,C6	C2	=AVERAGE(B2)
D2:D4,D6:D8	D2	=VLOOKUP(E2,CHOOSE({1,2},'2021'!D:D,'2021'!C:C),2,0)
C3,C7	C3	=AVERAGE(B2:B3)
C4,C8	C4	=AVERAGE(B2:B4)

 

[JTL9161]

Thank you. I will give it a try.