Sum of Double XLookup across multiple sheets?

[calebz]

We get a 11-month forecast from our customer on a weekly basis, that shows the upcoming 11 months and the quantities of all of the part numbers they expect to order from us.
So, at the end of the year, I will have 52 sheets of data that all have a different range of months and quantities.

What I was tasked to do is to make a chart that finds the average for the specific part number in the specific month across all 52 sheets.

I can get the total of a part number across all of the months in the sheet with this formula:

=SUM(XLOOKUP(A3,VSTACK('2025 Week (52):2025 Week (1)'!$A$2:$A$335),VSTACK('2025 Week (52):2025 Week (1)'!$B$2:$L$335)))

A3 = Part Number looking up
'2025 Week (52):2025 Week (1)'!$A$2:$A$335 = Columns of part numbers in the 52 sheets
'2025 Week (52):2025 Week (1)'!$B$2:$L$335 = the totals of those part numbers

What I need to do is do a nested XLookup that looks at the date as well:
(VSTACK('2025 Week (52):2025 Week (1)'!$B$1:$L$1)) = Rows of Date Headers in the 52 Sheets
but when I try it, it just gives me #Value!:

=Sum(XLOOKUP(A3,VSTACK('2025 Week (52):2025 Week (1)'!$A$2:$A$335),XLOOKUP(C2,VSTACK('2025 Week (52):2025 Week (1)'!$B$1:$L$1),VSTACK('2025 Week (52):2025 Week (1)'!$B$2:$L$335))))

Is there a different way to do lookup like this across multiple sheets without having to manually just to add up double XLookup for each sheet?

When I try doing a regular XLookup with the Vstack for the date rows instead of the Part numbers column, I get #Value!

 

[HighAndWilder]

We get a 11-month forecast from our customer on a weekly basis, that shows the upcoming 11 months and the quantities of all of the part numbers they expect to order from us.
So, at the end of the year, I will have 52 sheets of data that all have a different range of months and quantities.

What I was tasked to do is to make a chart that finds the average for the specific part number in the specific month across all 52 sheets.

I can get the total of a part number across all of the months in the sheet with this formula:

=SUM(XLOOKUP(A3,VSTACK('2025 Week (52):2025 Week (1)'!$A$2:$A$335),VSTACK('2025 Week (52):2025 Week (1)'!$B$2:$L$335)))

A3 = Part Number looking up
'2025 Week (52):2025 Week (1)'!$A$2:$A$335 = Columns of part numbers in the 52 sheets
'2025 Week (52):2025 Week (1)'!$B$2:$L$335 = the totals of those part numbers

What I need to do is do a nested XLookup that looks at the date as well:
(VSTACK('2025 Week (52):2025 Week (1)'!$B$1:$L$1)) = Rows of Date Headers in the 52 Sheets
but when I try it, it just gives me #Value!:

=Sum(XLOOKUP(A3,VSTACK('2025 Week (52):2025 Week (1)'!$A$2:$A$335),XLOOKUP(C2,VSTACK('2025 Week (52):2025 Week (1)'!$B$1:$L$1),VSTACK('2025 Week (52):2025 Week (1)'!$B$2:$L$335))))

Is there a different way to do lookup like this across multiple sheets without having to manually just to add up double XLookup for each sheet?

When I try doing a regular XLookup with the Vstack for the date rows instead of the Part numbers column, I get #Value!

Welcome to MrExcel

I think that you will find it much easier to store your data in one table instead of split between sheets.
Analysis will be much easier if it's all in one table.

As data comes in you just add it to the bottom.
Code can be written to do that each week.

Code can also be written to convert the data from your existing multiple sheets into the table.

Below is an example of how it could look.

Sum of Double XLookup across multiple sheets.xlsm
	A	B	C	D	E	F	G
1	Year Received	Week Received	Item	Forecast Year	Forecast Month	Quantity
2	2025	1	4681	2025	2	2138
3	2025	1	3124	2025	2	3599
4	2025	1	4726	2025	2	2359
5	2025	1	3371	2025	2	3627
6	2025	1	4409	2025	2	4507
7	2025	1	2833	2025	2	3346
8	2025	1	2060	2025	2	2508
9	2025	1	4694	2025	2	2690
10	2025	1	3751	2025	2	2317
11	2025	1	4187	2025	2	2626
12	2025	1	3857	2025	2	4137
13	2025	1	2906	2025	2	3645
14	2025	1	2441	2025	2	4947
15
Sheet1

 

[Sam_D_Ben]

Another way is,

Create a helper column, like in AA1:AA52 Formula

="2025 Week (" & ROW(A1) & ")"

And in B3: Formula

=SUM(MAP($AA$1:$AA$52, LAMBDA(sh, LET(r, MATCH($A3, INDIRECT("'"&sh&"'!A2:A335"), 0), c, MATCH(B$2, INDIRECT("'"&sh&"'!B1:L1"), 0), v, INDEX(INDIRECT("'"&sh&"'!B2:L335"), r, c), IFERROR(v, 0)))))