vlookup variable filepath one file variable can this be done via a function?

need to look up data across files. the file is held in the user area (don't ask why) and hence the file path changes depending on the user. I know how to find the user and hence know the full filepath but vlookup doesn't like variables.

Indirect and Indirect.ext only work if the files are all open which is no good.

Pull is too slow and doesn't always update.

Any ideas? is there a way to creat teh filepath on opening the file that vlookup will like since the only variable is the user?

any help greatly appreciated (not much of a coder I'm afraid)

I did try the following but still variable so vlookup didn't accept it.
Function Usrname() As String
Usrname = Environ("username")
End Function
Function pbfilearray() As String
'aim to create filearray for vlookup
pbfilearray = "'C:\Users\" & Usrname() & "\GD\SDE\[File.xlsm]inc lccy comm'!$A:$AZ"
End Function

then in vlookup(a1,pbfilearray(),3,false) but gives answer of #Value!

I was reading, as part of another problem that it is possible to read individual cells from a closed workbook. If this is so and you can't find a way to open it, then with a known structure for the look up table, you could build this cell by cell.
Having built the table in an active sheet, use it as your look up source. This assumes that you don't need to update the (closed)workbook, and you can set up an arrangement to be advised of any changes made to the table by persons other than you. Hope it works for you.

I decided to test this out, so I created a table of numbers in a file called Table.xlsx located at F20:J41 in Sheet1. I saved the file to C:\Temp and closed it.
Then I opened up a new workbook and entered the following into cell F20(sheet1):

='C:\Temp\[Table.xlsx]Sheet1'!F20
I then copied this formula to all cells F20:J41 and this replicated Table.xlsx without that workbook being open! I was then able to reference this replicated data in other formulae(eg VLOOKUP). Excel establishes a link with the original data and will prompt you to update for any amendments to the original data when you open the replications book.
There is a downside however. If the user makes structural alterations to the table as opposed to simply altering the values, you would need to be advised as such amendments might not be picked up by Excel (eg extending the size of the table). Of course it is likely that this problem is there whichever way you handle the task.

Hope that helps and good luck!