I have two workbooks, one contains my formulas/calculations, the other my data in multiple sheets.
I am trying to do a vlookup where the lookup array comes from a cell with a concat formula.

Example:

=VLOOKUP(B1,(vars!A17&'calc formula'!B2&vars!A15),MATCH($D4,[SALES.xlsx]NGFS_LD!$1:$1,0),FALSE)

B1: reference ID to search

A17&B2&A15

Is the reference to the second workbook: the text result of this part is: [SALES.xlsx]TEST!$A:$AF

Here A17 is [SALES.xlsx], B2 is TEST and A15 is !$A:$AF.

If I try to pass the concatenated string I get the #N/A result.

Is there a to perform this kind of operation where the reference array name in the vlookup function is a concatenation of other cells (strings)?