1

Alright, I am trying to figure out if the following is possible so that I can use it in later projects. I have been testing and working on this code in a sample workbook, which is why the file name is garbage. So don't judge me.

I have a VLOOKUP:

=VLOOKUP(A6,[dfhdfh.xlsx]Sheet1!$A:$B,2,FALSE)

This function currently works great. But I want to replace the static table_array value in the function to a cell reference, where I can enter a different file name at will.

Something along the lines of:

 =VLOOKUP(A3,CONCATENATE("[",F2,"]","Sheet1!$A:$B"),2,FALSE)

Where F2 contains the file name and extension dfhdfh.xlsx

But whenever I try to execute my VLOOKUP with it's nested CONCATENATE function, I get a #VALUE! error. What gives?

Improve this question
4
  • if your workbook dfhdfh.xlsx is always open, you can use =VLOOKUP(A3,INDIRECT("["&F2&"]Sheet1!$A:$B"),2,0). But if your wb is closed, INDIRECT doesn't work. In that case you need vba Commented Mar 25, 2014 at 20:24
  • Thanks, I appreciate the workaround. Would you be able to explain why the concatenate function doesn't work here though? That is what I am really getting at Commented Mar 25, 2014 at 20:30
  • 1
    At first, CONCATENATE(A1,A2) is the same as A1 & A2. At second, actually concatenation works and result of CONCATENATE("[",F2,"]","Sheet1!$A:$B") would be "[dfhdfh.xlsx]Sheet1!$A:$B", but excel doesn't recognize this string as reference, so you need to use Indirect for this purpose: INDIRECT(CONCATENATE("[",F2,"]","Sheet1!$A:$B")) gives you correct reference and entire formula would be: =VLOOKUP(A3,INDIRECT(CONCATENATE("[",F2,"]","Sheet1!$A:$B")),2,FALSE). But, using first point, you can make this formula shorter: =VLOOKUP(A3,INDIRECT("["&F2&"]Sheet1!$A:$B"),2,FALSE) Commented Mar 25, 2014 at 20:34
  • Ha, you had me at excel doesn't recognize this string as reference. That's what I needed to hear. Thanks! Commented Mar 25, 2014 at 20:39

1 Answer 1

Reset to default
1

Follow up from comments

If your workbook dfhdfh.xlsx is always open, you can use

=VLOOKUP(A3,INDIRECT("["&F2&"]Sheet1!$A:$B"),2,0).

But if your wb is closed, INDIRECT doesn't work. In that case you need VBA solution.

About your formula:

1) CONCATENATE(A1,A2) is the same as A1 & A2.

2) Actually concatenation works and result of CONCATENATE("[",F2,"]","Sheet1!$A:$B") would be "[dfhdfh.xlsx]Sheet1!$A:$B", but excel doesn't recognize this string as reference.

So you need to use Indirect for this purpose:
INDIRECT(CONCATENATE("[",F2,"]","Sheet1!$A:$B")) gives you correct reference.

Entire formula would be: 

=VLOOKUP(A3,INDIRECT(CONCATENATE("[",F2,"]","Sheet1!$A:$B")),2,FALSE).

But, using first point, you can make this formula shorter:

=VLOOKUP(A3,INDIRECT("[" & F2 & "]Sheet1!$A:$B"),2,FALSE)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.