Average of specific cells with numbers determined by values in Column/Row headers

[holycrapitsaduck]

I'm trying to create a formula on the first screenshot that references the cells in the second screenshot based on the name and week number. In the second screenshot, which shows data from the "HVL" tab, you can see the associate's name on the left and the week numbers across the top as the column headers. Because the week number appears more than once is where I believe I'm having the issue. The 100% that you see in the cell is not correct according to the data in the "HVL" tab. Here are some formulas that I've tried using:

=AVERAGE(((HVL!$A$2:$A$144='Weekly Report Card'!$C$1)*(HVL!$B$1:$AG$1='Weekly Report Card'!$A6)*(HVL!$C$3:$AG$201<>0)))
=AVERAGE(IF(HVL!A2:A144='Weekly Report Card'!C1:H1, IF(HVL!B1:AG1='Weekly Report Card'!A6, HVL!C3:AG201<>0, "ERROR")))
Both as an array and as normal.
=AVERAGEIF(HVL!$A$2:$A$144, "<>", INDEX(HVL!$C$3:$AG$201, MATCH($C$1, HVL!$A$2:$A$144, 0), MATCH('Weekly Report Card'!$A8, HVL!$C$1:$AG$1, 0)))
Both as an array and as normal.

If there's any other information that you need, please let me know.


-- removed inline image ---



-- removed inline image ---

 

[holycrapitsaduck]