Index Match Match total

[mgerving]

I have a workbook with 3 tabs, one is the list for the Index match (months), one is a list to total based on Data Val cells E2 & E3.
Is there a way to select, from Sheet 1, September and Week 1, grab the totals from E6 to E9, and paste them to the Blend tab cells B4 to B7, and then go back to Sheet 1 tab, choose week 2 and do the same, copy E6 to E9 on month and paste to C4 to C7 on Blend tab and still retain the '1' values?

 

[RustEE2020]

Can you post an example of the worksheets you are working with so we can get a better idea of what you are trying to do?
You can put dummy data in it if need be.

 

[mgerving]

Is there a way to upload the workbook?

 

[mgerving]

Try this using xl2bb

Book100.xlsx
	A	B	C	D	E
2				Month	Sept
3				Week	1
4
5		Base	Com 1	Com 2	Total
6	Joe	100	50	75	225
7	Tom	200	25	30	255
8	Sally	300	110	200	610
9	Betty	400	175	25	600
Sheet1

Cell Formulas
Range	Formula
B6	B6	=INDEX(Months!B3:E6,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B7	B7	=INDEX(Months!B10:E13,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B8	B8	=INDEX(Months!B17:E20,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
B9	B9	=INDEX(Months!B24:E27,MATCH($E$2,Month,0),MATCH($E$3,Person,0))
E6:E9	E6	=SUM(B6:D6)

Named Ranges
Name	Refers To	Cells
Month	=Months!$A$3:$A$6	B6:B9
Person	=Months!$B$2:$E$2	B6:B9

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=Month
E3:E4	List	=Person

	Week
	1	2	3	4
Joe	value from Sheet 1, Joe_Total (225)	value from Sheet 1, Joe_Total (625)	value from Sheet 1, Joe_Total (1025)	value from Sheet 1, Joe_Total (525)	AVERAGE(B4:E4)
Tom
Sally
Betty

 

[RustEE2020]

Do you have another sheet called Months? Us that where you data is stored? What exactly are you trying to do, if you just want the data to be shown like in the last example you can add a weekly number to your data on months then use indexes/matches to pull the data for the week number at the top of that column.

 

[mgerving]

yes, see below.

Book100.xlsx
	A	B	C	D	E
1		Joe
2		1	2	3	4
3	Sept	100	500	900	400
4	October	200	600	100	500
5	November	300	700	200	600
6	December	400	800	300	700
7
8		Tom
9		1	2	3	4
10	Sept	200	600	100	500
11	October	300	700	200	600
12	November	400	800	300	700
13	December	500	900	400	800
14
15		Sally
16		1	2	3	4
17	Sept	300	700	200	600
18	October	400	800	300	700
19	November	500	900	400	800
20	December	600	100	500	900
21
22		Betty
23		1	2	3	4
24	Sept	400	800	300	700
25	October	500	900	400	800
26	November	600	100	500	900
27	December	700	200	600	100
Months

What i am stuck on is how to capture the totals from E6 to E9 on Sheet 1, paste them into the Blend sheet in B4 to B7, go back to sheet 1, change the Weeks to '2' capture the values from E6 to E9, and paste those values into Blend sheet C4 to C7, and so on for '3' and '4' from Sheet 1.

 

[mgerving]

Is there anyone else that might be able to help with my question?

 

[mgerving]

I figured it out.

 

[smozgur]

Glad to hear you figured it out.

Do you mind posting your solution? Then it is perfectly fine to mark your post as the solution to help future readers.

Thanks.