Does anyone have an example of using a variable file name (and tab) in a vLookup? I have many files with many vLookups that have to be changed every month. It would be wonderful if I could only key the variable name once instead of copying and pasting so many vLookups. I've tried a number of different ways to accomplish this but have been unsuccessful. Please, if anyone has an example that works?
Jack Kent

That got the gary matter thinking

trick use
=INDIRECT()

and build the a cell up and them point the INDIRECT to it and it should work

Here's an example of the indirect function that Myle mentioned.

Joshua

Thank you both. I got this to work for one of the arguments but not the other. It works great with:

=VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),[file2.xlsx]Sheet1!$D$1,FALSE)

but if I use the same format on the third argument:

=VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),"'["&C16&".xlsx]"&D16&"'!$B$1,FALSE")

I get a #Value error message.

Any thoughts would be appreciated.

Jack Kent

It could be a number of things. I'd check in the following order:

1. Check workbook name to see that it is the same as C16 with neither having trailing spaces. Make sure it is an .xlsx and not an .xls

2. Check the worksheet/tab for the same along with D16

3. Other than that, I'd check your quote marks and concatenation. Excel can be finicky with that stuff.