vlookup on variable woorksheet

[JOEE1979]

On worksheet "ToolBox" I have

Wall Report Mar 11 - Mar 17.xlsx
	J	K
14
15
16	MEETING DATE:
17	March 9, 2023
18	LED BY:
19
20	SHIFT:
21	ON
22
23	Employees in
24	Attendance:
25
26		1
27		2
28		3
29		4
30		5
31		6
32		7
33		8
34		9
35		10
36		11
37		12
38		13
39		14
40		15
41		16
42		17
43		18
44		19
45		20
46		21
47		22
48		23
49		24
50		25
51		26
52		27
53		28
ToolBox

Cell Formulas
Range	Formula
J17	J17	=NOW()

Cells with Data Validation
Cell	Allow	Criteria
J19	List	=Supervisor
J21	List	AM, PM, ON

My other worksheets are

Wall Report Mar 11 - Mar 17.xlsx
	A	B	C	D	E	F	G	H
1		SAT	SUN	MON	TUES	WED	THURS	FRI
2	1	Akbar Fazlali	Andre DeJesus	Piotr Dryzmala	Piotr Dryzmala	Akbar Fazlali	Akbar Fazlali	Akbar Fazlali
3	2	Murad Thomas	Steve Hyshka	Kenny Ramkelawan	Kenny Ramkelawan	Murad Thomas	Murad Thomas	Murad Thomas
4	3	Andre DeJesus	Piotr Dryzmala	Andrew Harispuru	Andrew Harispuru	Peter Goudogianis	Andre DeJesus	Andre DeJesus
5	4	Steve Hyshka	Kenny Ramkelawan	Peter Goudogianis	Peter Goudogianis	Richard Ayson	Steve Hyshka	Steve Hyshka
6	5	Piotr Dryzmala	Andrew Harispuru	Richard Ayson	Richard Ayson	Jonathan Fonseca	Jonathan Fonseca	Ryan Campbell
AM

Wall Report Mar 11 - Mar 17.xlsx
	A	B	C	D	E	F	G	H	I
1		SAT	SUN	MON	TUES	WED	THURS	FRI
2	1	Pandeli Gaqollari	Colin Aselton	Florin Nitoiu	Florin Nitoiu	Pandeli Gaqollari	Pandeli Gaqollari	Pandeli Gaqollari
3	2	Brian Kailas	Greg Ganesh	John De Iuliis	John De Iuliis	Brian Kailas	Brian Kailas	Brian Kailas
4	3	Colin Aselton	Florin Nitoiu	Ryan Cowan	Ryan Cowan	Ryan Cowan	Colin Aselton	Colin Aselton
5	4	Greg Ganesh	John De Iuliis	Stephan Levasseur	Stephan Levasseur	Stephan Levasseur	Greg Ganesh	Greg Ganesh
6	5	Florin Nitoiu	Ryan Cowan	Stewart St Cyr	Stewart St Cyr	Stewart St Cyr	Harcharan Slatch	Alan Persaud
PM

Wall Report Mar 11 - Mar 17.xlsx
	A	B	C	D	E	F	G	H
1		SAT	SUN	MON	TUES	WED	THURS	FRI
2	1	Reme Ovbiagele	Dillon Defend	Jamie Cavanaugh	Jamie Cavanaugh	Reme Ovbiagele	Reme Ovbiagele	Reme Ovbiagele
3	2	Scott Lane	Jamie Cavanaugh	Fadi Sweidan	Fadi Sweidan	Scott Lane	Scott Lane	Scott Lane
4	3	Edward Herbacz	Fadi Sweidan	Jeff Daley	Jeff Daley	Edward Herbacz	Edward Herbacz	Edward Herbacz
5	4	Dillon Defend	Jeff Daley	Scott Cole	Scott Cole	Fadi Sweidan	Dillon Defend	Dillon Defend
6	5	Jamie Cavanaugh	Dave Kelly	Rocky Figliomeni	Rocky Figliomeni	Jeff Daley	Scott Cole
ON

From the 1st worksheet, based on cell"J21", I would like to VLOOKUP the worksheet with the same name.
Then based on today's WEEKDAY, I want the column to be copied to the 1st worksheet in J26-J53.

I have tried to use VLOOKUP in combination with WEEKDAY and even use INDIRECT, but I am unsuccessful.
I'm not sure if I'm overthinking it or if I'm way off.
I need help, Thanks

 

[Peter_SSs]

The function NOW() included date and time. Since you appear to only want the date in J17, I suggest that you use =TODAY() instead.

See if this does what you want.

JOEE1979.xlsm
	J
16	MEETING DATE:
17	March 10, 2023
18	LED BY:
19
20	SHIFT:
21	ON
22
23	Employees in
24	Attendance:
25
26	Reme Ovbiagele
27	Scott Lane
28	Edward Herbacz
29	Dillon Defend
30
31
ToolBox

Cell Formulas
Range	Formula
J17	J17	=TODAY()
J26:J29	J26	=LET(c,WEEKDAY(J$17,16)+1,r,INDIRECT("'"&J$21&"'!R2C"&c&":R50C"&c,0),FILTER(r,r<>""))
Dynamic array formulas.