Embedded CONCATENATE function to specify table array for VLOOKUP

[helamansanchez]

Hi all --

I am trying to build a spreadsheet that will act as a dashboard that pulls data from various other workbooks. Specifically, I have a drop-down menu in cell B2 where I can select a specific product and a drop-down menu in cell C2 where I can select year. I am trying to create a VLOOKUP formula that will pull data for each line item on the financial statement for whichever product is selected for the specific year that has been selected.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer1[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Product1 *dropdown*[/TD]
[TD]2016 *dropdown*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Initial Approval[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Total Revenue[/TD]
[TD]$xxxx[/TD]
[TD]$xxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'C:\Users\xyz123\Documents\[Summary Customer1 2016.xlsx]Product1'!$B$1:$T$88[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Manufacturing Expense[/TD]
[TD]$xxxx[/TD]
[TD]$xxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each time a new product is selected, a VLOOKUP formula in cell Z1 returns the customer that corresponds to that product. The data that I have is organized into spreadsheets for a single customer and a single year. For example, I may have a folder with the following Excel workbooks: "Summary Customer1 2015" ; "Summary Customer1 2016" ; "Summary Customer2 2015" ; "Summary Customer2 2016", etc. Each of those workbooks then has a separate tab for each product that we sell to that customer.

Since the data I need is in a separate file, I have created a VLOOKUP formula that will pull the correct data from the file. Here is an example of what I put in cell D5:
=VLOOKUP(B5,'C:\Users\xyz123\Documents\[Summary Customer1 2016.xlsx]Product1'!$B$1:$T$88,19,FALSE)

This works fine, except I would like the reference to be dynamic, so that when someone selects a different option in cell B2, the table array used in the VLOOKUP changes to the correct file and tab.

I tried to solve this problem by creating a CONCATENATE function in cell Z5 that pulls together the selected options in the dropdowns in B2 and C2, and the correct customer from Z1 to change the table array reference to the correct file.

=CONCATENATE("'C:\Users\xyz123\Documents\[Summary ",Z1," ",C2,".xlsx]",B2,"'!$B$1:$T$88")

This spits out the same table array reference that I used earlier in the hard-coded version:
'C:\Users\xyz123\Documents\[Summary Customer1 2016.xlsx]Product1'!$B$1:$T$88

And then I use this in the VLOOKUP in cell D5 formula like so:
=VLOOKUP(B5,Z5,19,FALSE)

When I reference Z5 within the VLOOKUP, it returns #N/A, even though if I manually type in the value that the CONCATENATE formula spits out into the VLOOKUP argument, it returns the correct number from the Summary Customer1 workbook.

Any ideas why this is, and what I could do to create this dynamic reference within my VLOOKUP?

Thank you in advance for any ideas you may have.

P.S. A couple things that may be helpful to note:
-After I manually type in the file address into the VLOOKUP, Excel shortens the reference to simply '[Summary Customer1 2016.xlsx]Product1'!$B$1:$T:$88
-All of the Customer files are in the same folder as this dashboard file that I am creating.