Need help pulling some data out of a table

[Gadman]

I have a table that holds raw values for certain areas. I need a can't seem to find the right index/match formula to get what I want out of it

I need 2 formulas to do 2 things.

1. Search through the table and give the value for a particular area on a particular week. For example Area 1 on Week 49 = 66
2. Average the Area's values over the a given period. For example: The average of Area 1's values on Period 12 is 66: Week 48= 1 Week 49=66 Week 50=131 so (1+66+131)/3=66.

Thanks for the help!

Year	Quarter	Period	Week	Area	Value
FY20	4​	12​	48​	Area 1	1.00​
FY20	4​	12​	48​	Area 2	6.00​
FY20	4​	12​	48​	Area 3	11.00​
FY20	4​	12​	48​	Area 4	16.00​
FY20	4​	12​	48​	Area 5	21.00​
FY20	4​	12​	48​	Area 6	26.00​
FY20	4​	12​	48​	Area 7	31.00​
FY20	4​	12​	48​	Area 8	36.00​
FY20	4​	12​	48​	Area 9	41.00​
FY20	4​	12​	48​	Area 10	46.00​
FY20	4​	12​	48​	Area 11	51.00​
FY20	4​	12​	48​	Area 12	56.00​
FY20	4​	12​	48​	Area 13	61.00​
FY20	4​	12​	49​	Area 1	66.00​
FY20	4​	12​	49​	Area 2	71.00​
FY20	4​	12​	49​	Area 3	76.00​
FY20	4​	12​	49​	Area 4	81.00​
FY20	4​	12​	49​	Area 5	86.00​
FY20	4​	12​	49​	Area 6	91.00​
FY20	4​	12​	49​	Area 7	96.00​
FY20	4​	12​	49​	Area 8	101.00​
FY20	4​	12​	49​	Area 9	106.00​
FY20	4​	12​	49​	Area 10	111.00​
FY20	4​	12​	49​	Area 11	116.00​
FY20	4​	12​	49​	Area 12	121.00​
FY20	4​	12​	49​	Area 13	126.00​
FY20	4​	12​	50​	Area 1	131.00​
FY20	4​	12​	50​	Area 2	136.00​
FY20	4​	12​	50​	Area 3	141.00​
FY20	4​	12​	50​	Area 4	146.00​
FY20	4​	12​	50​	Area 5	151.00​
FY20	4​	12​	50​	Area 6	156.00​
FY20	4​	12​	50​	Area 7	161.00​
FY20	4​	12​	50​	Area 8	166.00​
FY20	4​	12​	50​	Area 9	171.00​
FY20	4​	12​	50​	Area 10	176.00​
FY20	4​	12​	50​	Area 11	181.00​
FY20	4​	12​	50​	Area 12	186.00​
FY20	4​	12​	50​	Area 13	191.00​

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Year	Quarter	Period	Week	Area	Value		Area	Week	Value
2	FY20	4	12	48	Area 1	1		Area 1	49	66
3	FY20	4	12	48	Area 2	6		Area 1	12	66
4	FY20	4	12	48	Area 3	11
5	FY20	4	12	48	Area 4	16
6	FY20	4	12	48	Area 5	21
7	FY20	4	12	48	Area 6	26
8	FY20	4	12	48	Area 7	31
9	FY20	4	12	48	Area 8	36
10	FY20	4	12	48	Area 9	41
11	FY20	4	12	48	Area 10	46
12	FY20	4	12	48	Area 11	51
13	FY20	4	12	48	Area 12	56
14	FY20	4	12	48	Area 13	61
15	FY20	4	12	49	Area 1	66
16	FY20	4	12	49	Area 2	71
17	FY20	4	12	49	Area 3	76
18	FY20	4	12	49	Area 4	81
19	FY20	4	12	49	Area 5	86
20	FY20	4	12	49	Area 6	91
21	FY20	4	12	49	Area 7	96
22	FY20	4	12	49	Area 8	101
23	FY20	4	12	49	Area 9	106
24	FY20	4	12	49	Area 10	111
25	FY20	4	12	49	Area 11	116
26	FY20	4	12	49	Area 12	121
27	FY20	4	12	49	Area 13	126
28	FY20	4	12	50	Area 1	131
29	FY20	4	12	50	Area 2	136
30	FY20	4	12	50	Area 3	141
31	FY20	4	12	50	Area 4	146
32	FY20	4	12	50	Area 5	151
33	FY20	4	12	50	Area 6	156
34	FY20	4	12	50	Area 7	161
35	FY20	4	12	50	Area 8	166
36	FY20	4	12	50	Area 9	171
37	FY20	4	12	50	Area 10	176
38	FY20	4	12	50	Area 11	181
39	FY20	4	12	50	Area 12	186
40	FY20	4	12	50	Area 13	191
List

Cell Formulas
Range	Formula
J2	J2	=SUMIFS(F:F,D:D,I2,E:E,H2)
J3	J3	=AVERAGEIFS(F:F,E:E,H3,C:C,I3)

 

[Gadman]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Year	Quarter	Period	Week	Area	Value		Area	Week	Value
2	FY20	4	12	48	Area 1	1		Area 1	49	66
3	FY20	4	12	48	Area 2	6		Area 1	12	66
4	FY20	4	12	48	Area 3	11
5	FY20	4	12	48	Area 4	16
6	FY20	4	12	48	Area 5	21
7	FY20	4	12	48	Area 6	26
8	FY20	4	12	48	Area 7	31
9	FY20	4	12	48	Area 8	36
10	FY20	4	12	48	Area 9	41
11	FY20	4	12	48	Area 10	46
12	FY20	4	12	48	Area 11	51
13	FY20	4	12	48	Area 12	56
14	FY20	4	12	48	Area 13	61
15	FY20	4	12	49	Area 1	66
16	FY20	4	12	49	Area 2	71
17	FY20	4	12	49	Area 3	76
18	FY20	4	12	49	Area 4	81
19	FY20	4	12	49	Area 5	86
20	FY20	4	12	49	Area 6	91
21	FY20	4	12	49	Area 7	96
22	FY20	4	12	49	Area 8	101
23	FY20	4	12	49	Area 9	106
24	FY20	4	12	49	Area 10	111
25	FY20	4	12	49	Area 11	116
26	FY20	4	12	49	Area 12	121
27	FY20	4	12	49	Area 13	126
28	FY20	4	12	50	Area 1	131
29	FY20	4	12	50	Area 2	136
30	FY20	4	12	50	Area 3	141
31	FY20	4	12	50	Area 4	146
32	FY20	4	12	50	Area 5	151
33	FY20	4	12	50	Area 6	156
34	FY20	4	12	50	Area 7	161
35	FY20	4	12	50	Area 8	166
36	FY20	4	12	50	Area 9	171
37	FY20	4	12	50	Area 10	176
38	FY20	4	12	50	Area 11	181
39	FY20	4	12	50	Area 12	186
40	FY20	4	12	50	Area 13	191
List

Cell Formulas
Range	Formula
J2	J2	=SUMIFS(F:F,D:D,I2,E:E,H2)
J3	J3	=AVERAGEIFS(F:F,E:E,H3,C:C,I3)

You're the best!

 

[Fluff]

You're welcome & thanks for the feedback.