Dynamic range in match and index

[Skiff]

Hi.
I've got very distinct problem with formulas. I need to dynamically detect column for vlookup or index/match combination to get relevant history data

I create manually sheet composed of history data (just a part of data, but structure is visible):

2022-01		2022-02		2022-03		2022-04		2022-05		2022-06		2022-07
name1	0​	name1	0,045​	name1	0​	name1	0​	name1	0,063​	name1	0,12​	name1	0​
name2	0,077​	name2	0,048​	name4	0​	name4	0​	name4	0​	name4	0​	name4	0​
name3	0,091​	name3	0,045​	name2	0​	name2	0,125​	name2	0,074​	name2	0,034​	name2	0,07​

This is basically two columns copied from pivot table of each months report.

And then into new month I've connect this data for each name. So when I have report for 2022-10 then I get 3 history columns data from above (see last 3 columns)

	denominator	numerator	%	pp	2022-07	2022-08	2022-09
Sum	916​	21​	2,3%	-11,6pp​	4,0%	3,6%	13,9%
name1	1​	0​	0,0%
name2	15​	0​	0,0%	-14,3pp​	0,0%	0,0%	14,3%
name3	46​	0​	0,0%	-2,0pp​	0,0%	0,0%	2,0%
name4	31​	0​	0,0%	-17,1pp​	7,0%	2,6%	17,1%

So I've managed to work it out with INDIRECT since offset results in #NA error

=VLOOKUP($A5,INDIRECT(CONCAT("'[hist.xlsx]hist'!C",MATCH(TEXT(F$3,"yyyy-mm"),'[hist.xlsx]hist'!$1:$1,0),":C",MATCH(TEXT(F$3,"yyyy-mm"),'[hist.xlsx]hist'!$1:$1,0)+1),0),2,0)

but historical data is placed in another workbook so it needs to be open for values to appear.
And this is not acceptable because report is distributed by email to end users.

Last resort would be putting archive data into report and hiding those sheets. But first maybe some one has another idea how to get this data (without VBA).

Anyone has any workaround to it?