CODEWITHSUNDEEP
excel
user2993456
user2993456

Reputation:

Dynamic table_array value for Vlookup function

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?

Upvotes: 1

Views: 6433

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

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)

Upvotes: 1

Related Questions