Dynamic vlookup formula

[Rszink77]

Hi I have been trying to figure out how to create a dynamic table array for my vlookup that references a different workbook. I would like the sheet name to be dynamic using a specific cell value from the original workbook. Each sheet is saved as a date in mm dd yyyy format. I would like to replace the 01 08 2022 with cell (D2) Is this be possible to do? I have gotten errors every way I've tried and am stumped!

=VLOOKUP(A2,'[FC YTD.xlsm]01 08 2022'!$A:$AZ,37,0)

 

[etaf]

=VLOOKUP(A2,indirect("'[FC YTD.xlsm]"&D2&"'!$A:$AZ"),37,0)

I have modified to work on a simple sheet using, and its name for my system - BUT above should work with OPEN books

=VLOOKUP(A2,INDIRECT("'[FC YTD.xlsx]"&D2&"'!$A:$B"),2,0)
and it work - BUT with the workbook needs to be open at the same time

do you want to work with the workbook closed ?

test.xlsx
	A	B	C	D	E
1	#N/A	2
2	b			01 08 2022
3
4
Sheet2

Cell Formulas
Range	Formula
A1	A1	=VLOOKUP(A2,'[FC YTD.xls]01 08 2022'!$A:$B,2,0)
B1	B1	=VLOOKUP(A2,INDIRECT("'[FC YTD.xlsx]"&D2&"'!$A:$B"),2,0)

FC YTD.xlsx
	A	B
1
2	a	1
3	b	2
4	c	3
5
01 08 2022

 

[Rszink77]

Thank you! I would like it to work with a closed sheet if possible

 

[etaf]

i have been looking how to use with a closed workbook, and as far as i can work out its not possible with INDIRECT(), other members my know a way

you may ned to be using VBA for a solution to a closed workbook, but i dont supply solutions to VBA as my knowledge is to outdated now

there are some comments that it may work with the full path - but as i'm on a MAC OSX - not sure on the path for my example - really used to windows paths
c:/user/... etc type path

as you are using office 2021 , it may have the LET function and something may be possible using that - but i dont know myself

I will see if i can work out how to enter a full path in Excel for Mac and test - so may reply later , otherwise hopefully other members will know