Need combined average over multiple years using Vlookup or averageif?

samst

Board Regular
Joined
Feb 12, 2003
Messages
71
My Data basically looks like the table below...

I'm trying to figure out a formula that will look up the letter in Sheet 2 find the corresponding letter in Sheet 1 and then determine the average of the 2017 and 2018 corresponding month.

For example
For the letter A the formula should return the value 75.1 and for the letter B the value should be 959.5



Sheet 1
2017 2018
1/9/2017 1/16/2017 1/23/2017 1/30/2017 2/6/2017 1/2/2018 1/9/2018 1/16/2018 1/23/2018 1/30/2018
A 12 161 33 53 68 42 98 123 81 73
B 117 1108 1140 1145 1048 1192 957 896 1121 1389
C 12 73 71 61 87 82 89 86 80 71
D 12 138 99 59 72 93 87 119 95 104
E 3 6 4 4 5 23 44 85 15
F 736 6030 3248 3203 3748 2646 3630 3019 3147 1909
G 216 1523 995 690 784 472 612 633 835 415

Sheet 2

A Jan Need combined average of Jan 2017 and 2018
B Jan Need combined average of Jan 2017 and 2018
C Feb Need combined average of Feb 2017 and 2018
D Feb Need combined average of Feb 2017 and 2018
E Feb Need combined average of Feb 2017 and 2018
F Feb Need combined average of Feb 2017 and 2018
G Feb Need combined average of Feb 2017 and 2018
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top