VLOOKUP with Concatenation

[CMLittle]

I'm trying to populate fields in a (transactional) workbook based on data from a named range in another workbook (master data). I've successfully done this in the past by specifically defining the file and range name in the master data workbook in a VLOOKUP (but I'm looking for a more generic solution now).
Here is where I've had success:
=VLOOKUP(Y2,'EH_De-Consolidation Workbook.xls'!CNIG_5000,3,FALSE)
In this case, I'm populating the profit centre in N2 based on an evaluation of the contents of Y2 against data in the EH_De-Consolidation Workbook (master data workbook), specifically the named range CNIG_5000. It returns CNIG-DIV in this case.
Now, I'd like to make the data workbook more generic so that one workbook can be used for any of our legal entities (not just 5000).
In the data workbook, I've populated a range (DISTRICT_RANGE) in the transactional workbook with information which will allow me to reference the correct range in the master data workbook. Here is an example of data in the DISTRICT_RANGE range:
3300 PTDJ_3300
3400 CTM_3400
5000 CNIG_5000

Here is the formula that is giving me difficulties:
=VLOOKUP(Y2,CONCATENATE("'EH_De-Consolidation Workbook.xls'!",VLOOKUP(A2,DISTRICT_RANGE,3,FALSE)),3,FALSE)
The legal entity reference (5000 in this case) is in A2.
The master data workbook name will never change (so it can be hard-coded in this formula) but the range name changes based on the legal entity.
My problem is with the concatenation of the master data file name and the VLOOKUP of the range in the master data workbook - CONCATENATE("EH_De-Consolidation Workbook.xls!",VLOOKUP(A4,DISTRICT_RANGE,3,FALSE))
While it evaluates just fine by itself - 'EH_De-Consolidation Workbook.xls'!CNIG_5000 - it doesn't work with the first VLOOKUP as #VALUE! is returns from this portion of the formula.

I have also tried using ampersands in place of the CONCATENATE without success. I've looked at ADDRESS, HYPERLINK, INDIRECT functions but I don't believe they resolve my problem.

Any ideas to help overcome this are really appreciated!

 

[Oaktree]

Welcome to the board.

Try:

=VLOOKUP(Y2,INDIRECT(CONCATENATE("'EH_De-Consolidation Workbook.xls'!",VLOOKUP(A2,DISTRICT_RANGE,3,FALSE))),3,FALSE)

Note that, when using INDIRECT to reference a different file, you'll get a #REF! error if the referenced file is closed.

 

[CMLittle]

Cannot THANK YOU enough! Your solution did exactly what I needed.

I'll put something in one of my first macros in this workbook to open the master data workbook. I reference it so much in the other formulas that it would make all the calculations go faster anyway.