Search for a value in a worksheet and based on its address add another value to an average

Hi guys. Hopefully someone with a better understand of Excel than me can point me on the right path here. Been trying everything with Index, Match, Address functions, but still no luck with this.

So I have this main worksheet "Dashboard", where I want to showcase a value of an average.

Then, I have a different worksheet "EngagementRate", where I broke down each week of the year, and added a field for a weekly 'Engagement Rate' value to be added.

What I mean to do is this. Go through every value in Column 1 of the "EngagementRate" worksheet, and if the string within the cell matches another given string (say "January"), then based on the address of that found cell, I could point to my Engagement Rate value. In this case if A3 = "January" that would be Row = 3, Column = 1. And my Engagement Rate value could be calculated by pointing at Row+2, and Column+1, since they all follow the same structure over and over. And then I would like to add that Engagement rate value I just calculated to an average. And all of this by pointing to the "EngagementRate" worksheet from the "Dashboard" one, where I want my final average showcased.

I hope that made even a tiny bit of sense, and I'm happy to receive any tips to make this work. Been driving me crazy so far. I've added a small worksheet where I added only these fields.

Thank you guys!

Try in A2 and copy down:

Formula:

Please Login or Register  to view this content.

Good luck!

Thank you very much for your help. However this posed another problem down the line when I tried to replicate it for another set of data. Since all of these monthly structures repeat until December (both within the Dashboard worksheet, and within the other secondary worksheets) I was able to make the formula work for the first month, but it won't work for months 2-12.

I have created another Test sheet for the new data structure that I'd appreciate if you could take a look at. I couldn't even get it to work within this worksheet at all this time. I am really confused about what I am doing wrong here. Hopefully you could help me out again, and thank you for your time!

I recommend to change your data format to the in "FollowersGroth2" as you current format is far from practical

=IFERROR(AVERAGEIFS(FollowersGrowth2!C4:C10,FollowersGrowth2!$A4:$A10,">="&Dashboard!$A3,FollowersGrowth2!$A4:$A10,"<="&EOMONTH(Dashboard!$A3,0)),"")

For the new file in post #3, this formula in A2:

Formula:

Please Login or Register  to view this content.

And this in D2:

Formula:

Please Login or Register  to view this content.

Note that in the first formula, the criteria range starts in A3, where the first instance of MONTH is entered, and goes down to A100, or any other row of your choosing.
The average range starts in A6, where the first instance of VALUE is entered, and goes down to A103, so both ranges have the same number of rows.

Also, note that the AVERAGEIF function ignores blank cells.