Indirect reference of 2 cells in "sheet1" to look up value in table "table1"

[mkseto]

I have "table1" with dates of 4 months from Nov/2024 to Feb/2025 in the top row starting in A2 (i.e. B2=Nov/1/2024, C2=Nov/2/2024, D2=Nov/3/2024 ... etc.). In the same "table1", the first column lists a bunch of names (A2=John, A3=Dave, A4=Mary .... etc.). The table contains the account balances for each person for each day in the 4 months. For example, the second row lists John's account balances everyday (including weekends) in the 4 months across row #2.
The same workbook has "sheet1" that contains the following amongst all the columns:
"M" = names (e.g. Dave, John, Mary)
"W" = random dates within the 4 months Nov/2024 to Feb/2025 (e.g. Jan/07/2025, Nov/20/2024, Feb/21/2025)
"Z" = whole numbers from 0 to 160

I'm trying to build a complicated formula in column AA for sheet1 to meet the following:
1) Use the name in "M" and date in "W" to find the corresponding value in "table1" (e.g. M2="John" and W2=Nov/05/2024, retrieve account balance for John on Nov/05/2024 in table1 and place in cell AA2)
2) Use the value in "Z" and repeat the above to fill the adjacent "Z less 1" cells. (e.g. using same example above, if Z2=4, then pull the account balance for John for 3 (i.e. 4-1) dates from Nov/6/2024 & Nov/7/2024 & Nov/8/2024 and place the values in cells AB2 & AC2 & AD2). *** in other words, "Z" is the total number of days we need the balances.

I'm hoping to avoid using macro, so I think some type of IF/THEN logic could work to satisfy #2 above by copying the formula from AA to fill the rest of each row so that in the above example of Z2=4, the formula will retrieve values to place in AA2 to AD2 and then with "N/A" beyond AD2.

After trying for weeks with no luck, I have come to realize this is well beyond my limited Excel skills, so I'm hoping someone can help me accomplish this.

 

[Kaper]

AA2 and copy right:

=IF(COLUMNS($AA$1:AA$1)<=$Z2,INDEX(table1!$B$2:$DR$30,MATCH($M2,table1!$A$2:$A$30,0),MATCH($W2-1+COLUMNS($AA$1:AA$1),table1!$B$1:$DR$1,0)),"")

Could shorter and more important - a spill formula if you use Excel version 365 (2021 shall probably do the work too, so my suggestion is - edit your profile and add excel version info).
Sheet table1:

Book1
	A	B	C	D	E	F	G	H	I	J
1		10.02.2025	11.02.2025	12.02.2025	13.02.2025	14.02.2025	15.02.2025	16.02.2025	17.02.2025	18.02.2025
2	John	1	2	3	4	5	6	7	8	9
3	Amy	15	14	13	12	11	10	9	8	7
4	Ella	6	7	8	9	10	11	12	13	14
table1

Sheet Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2													Amy										15.02.2025			3	10	9	8
3
Sheet1

Cell Formulas
Range	Formula
AA2:AE2	AA2	=IF(COLUMNS($AA$1:AA$1)<=$Z2,INDEX(table1!$B$2:$DR$30,MATCH($M2,table1!$A$2:$A$30,0),MATCH($W2-1+COLUMNS($AA$1:AA$1),table1!$B$1:$DR$1,0)),"")

 

[Kaper]

Just in case a spill formula (so no copying to the right needed) proposition could be:

=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2													Amy										15.02.2025			3	10	9	8
3
Sheet1

Cell Formulas
Range	Formula
AA2:AC2	AA2	=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))
Dynamic array formulas.

 

[mkseto]

Just in case a spill formula (so no copying to the right needed) proposition could be:

=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))

Wow, you are super FAST, I was still trying to google various functions and thought there's already a response!
I copied the different formula above but somehow getting "#N/A" in the cells I was expecting values (some other cells with "0" in column "Z" returns blanks, which is good). I'm sure I must have done something wrong so let me double check. By the way, after I copied the formula, I get a popup "Update values: table1" window and I just close it ... is that expected and am I supposed to select a file?
Again, appreciate the quick response!!!

 

[Kaper]

In the formulas instead of table1 use a sheet name, where table 1 is located.
You have not mentioned it.
You have only said the name of the "output sheet": >>The same workbook has "sheet1"<<

 

[mkseto]

In the formulas instead of table1 use a sheet name, where table 1 is located.
You have not mentioned it.
You have only said the name of the "output sheet": >>The same workbook has "sheet1"<<

My apology for not being clear in my original post (and I noticed some typo's as well
Your first solution definitely works .... I'm using Excel 2013, therefore couldn't try the other formula
Thanks a million for the quick/super help.
I will now need to figure out why when I apply the same formula to another spreadsheet but didn't work (large file, fields are slightly different but same structure, but getting the #n/a error. Anyway, I'm sure I can get your formula to work on that sheet).
Thanks again!!!!!

 

[Kaper]

Glad we could help.

As for #NA in other workbook: For me first thing to be checked is: whether all dates are real Excel dates (both in source table and in W2. Easy test - change format of a cell containing a date to number. If the value display now as 5digits number, like 45705.00 or similar, it is proper dates. If not - it is probably just texts looking as a date. Second point - names. They have to match exactly. So >John< is not the same as >John < (with space ar the end) and John Black is different than John Black where a space in one case is just a standard space and in the other a non-breaking space (ASCII character 160). etc.

 

[mkseto]

Glad we could help.

As for #NA in other workbook: For me first thing to be checked is: whether all dates are real Excel dates (both in source table and in W2. Easy test - change format of a cell containing a date to number. If the value display now as 5digits number, like 45705.00 or similar, it is proper dates. If not - it is probably just texts looking as a date. Second point - names. They have to match exactly. So >John< is not the same as >John < (with space ar the end) and John Black is different than John Black where a space in one case is just a standard space and in the other a non-breaking space (ASCII character 160). etc.

I finally got it fixed: I tested the first "MATCH" statement and it yielded the match results, so that was fine. Testing the second "MATCH" yielded the #N/A error, so I knew that was what I had to look into. Long story short, the date field was really messy in both "table1" and "sheet1" (source data was manually entered). It was easy enough to clean up table1 since date were sequential, it was much more challenging for "sheet1"
I really need to THANK YOU yet another time, there's no way I could come up with this formula (and I had googled it for weeks). Once more thing, I was able to find a PC with Excel 365 to test the other spill formula: it worked perfectly!!!
Thank you again, you ROCK!!!

 

[Kaper]

You made my evening with these comments.

And what I appreciate most is that you use my help as a kind of mentoring or "showing a direction" and do the details by yourself.