Lookup a Column Range by Matching Heading

[jcoeng]

Hello!

I've got an accounting spreadsheet that is used to total up various items. One item is provided in a pivot table that I can't modify but changes over time.

I have a working solution but references are hard-coded. I need help making the lookups dynamic based on column headers. More details in the linked file and mini sheet below.

Fruit totals.xlsx

1drv.ms

Mini-sheet below

Fruit totals.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	This table's content grows longer as new fruits are added							This table also grows as fruits are added and as time goes on
3	New colors may be added, can be in alphabetical order							New fruits will be in alphabetical order so hard coded column references break
4								I don’t have the color in the main pivot table
5
6	Fruits grouped by color							Pivot table of pounds of fruit sold each month
7	Green Fruit	Orange Fruit	Red Fruit					Year	Month	Apples	Cateloupe	Cherries	Kiwi	Mango	Watermelon
8	Kiwi	Mango	Cherries					2023	1	10	7	5	3	4	20
9	Watermelon	Canteloupe	Apples					2023	2	15	5	3	7	8	22
10								2023	3	7	8	9	5	7	18
11								2023	4	15	9	4	4	6	14
12
13	Table to fill needs the same formula in each cell
14	Correlate date shown with year and month in pivot table
15	Find the column color group by matching header
16	Look only at fruits in the color groups,
17	Sum all values in each color group for each month
18	Existing formula works but breaks easily when pivot table updates with new data
19	The tables below are representative, in use they have other data in rows between the monthly totals so I cant use a separate pivot table
20	Table below uses hard coded references
21
22		Green Fruit	Orange Fruit	Red Fruit
23	2023-01-31	23
24	2023-02-28
25	2023-03-31
26	2023-04-30
27
28	desired result
29		Green Fruit	Orange Fruit	Red Fruit
30	2023-01-31	23	11	15
31	2023-02-28	29	13	18
32	2023-03-31	23	15	16
33	2023-04-30	18	15	19
Sheet1

Cell Formulas
Range	Formula
C23	C23	=INDEX(N8:N11,MATCH(1,(I8:I11=YEAR(B23))*(J8:J11=MONTH(B23)),0))+INDEX(P8:P11,MATCH(1,(I8:I11=YEAR(B23))*(J8:J11=MONTH(B23)),0))

I've cross-posted a similar question in the google sheets forum but no replies over there yet. The primary work is in GSheets so if there are tricks to use that make it easier than excel, please let me know.

Thank you!

 

[awoohaw]

Is this going to be in Excel or Google Sheets?
If excel what is your version of excel? (Please update your profile so your version appears in your button.)

An excel solution may not work with google sheets.

 

[awoohaw]

Here is a pivot table that updates when you put the data in Tables and use power query.
I made this in the linked workbook. You can open it there.

Fruit totals.xlsx
	A	B	C	D	E	F	G	H
35
36
37
38		Sum of Sales	Column Labels
39		Row Labels	Green Fruit	Orange Fruit	Red Fruit	Grand Total
40		2023
41		1	23	11	15	49
42		2	29	13	18	60
43		3	23	15	16	54
44		4	18	15	19	52
45		Grand Total	93	54	68	215
46
47
Sheet1

 

[jcoeng]

I added the info to my profile, thank you for letting me know it was there. Ultimately the solution will be needed in google sheets,

The data gathered from the pivot table will be mixed into another sheet on a per-row basis with other transactions so ultimately I think I'll need a formulaic solution but maybe I can create a helper table...hmmm

I was able to get a partial solution using match and address to find the starting cell of each range, I just couldn't turn that into a usable range for the index function.