Vlookup with 2 cell as file names

[freeon]

I'm using the below formula were C3 + D3 is the file name were to lockup a value. It works good except if C3 is My and D3 File it only works if the file is saved as MyFile and doesn't work if it's saved as My File with a space. anyway to make this work.

=VLOOKUP(A3,INDIRECT("'["&C3&D3&"]Sheet1'!A:J"),10,0)

[Tony Valko]

Try it like this...

=VLOOKUP(A3,INDIRECT("'["&C3&" "&D3&"]Sheet1'!A:J"),10,0)

[freeon]

Perfect. Thanks

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[freeon]

Done. Didn't know about that feature

[freeon]

I spoke to soon. Not sure what I did but now I keep getting #REF. If I click on show calculation step it's showing me the file name it's looking for and it's the exact same name with the space as the file I want to get the information from. Both files are save in the same folder and are both open. What could have happened. It was working at first.

[popipipo]

With these 2 files it works

[Tony Valko]

Works OK for me. Both files must be open.

[freeon]

Well that is strange. I opened both your files at the same time and I get the #REF

[popipipo]

Save then in the same dir and open Excel.
Open the files using excel ('File' 'Open' Browse to the directory and open the files.)
Don't open the file by clicking the filenames

[freeon]

Saved both files to the same folder. Closed both files and then re opened both files and still get #REF

[popipipo]

Did you see I edited my answer?

[freeon]

Tried (Open the files using excel ('File' 'Open' Browse to the directory and open the files.) and still get #REF

[popipipo]

If you go to 'View' 'Switch windows' do you see both file names?

[freeon]

Yes I see both file names.

[popipipo]

My last idea: calculation on manual or automaticaly

[freeon]

If you mean in the excel option workbook calculation it's set to automatic.

[popipipo]

yes that is what I meant

[freeon]

This is strange. It worked this morning and now it won't work anymore. I thought maybe I change a setting or something without noticing. I just remote connected to my home computer and excel on my home computer should still have factory setting because a rarely use. I save your files and open from the file menu and I still get #REF.