Indirect in Vlookup

[jessica_p]

Hi all,

So I've been looking on the internet and trying myself, but I can't seem to make it work.
I'm looking to make a formula that's changing every month, so want to use INDIRECT.

The file has two sheets: a tab calles "Parameters" and an information tab to be filled.
On the information tab, in column B I want to VLOOKUP information based on the information in column A.
Only, I want the VLOOKUP to look up the information in the file mentioned in cel C13 and cel C14 in the parameters tab, since these cels are used in VBA for looking up the information in column A on the information tab. These cells and therefore the file to look up in change every month.

Without the INDIRECT, not looking at the Parameters tab, the VLOOKUP would look like this:

VLOOKUP($A6,'I:\Miscellaneous\01. Input\[EMEA.xlsx]Netherlands'!$A:D,4,0)

So then I would have to manually change the formula each month.

On the Parameters tab:
In cel C3 is an ever changing country mentioned via a , in this case "Netherlands".
In cel C13 is mentioned "I:\Miscellaneous\01. Input"
In cel C14 is mentioned "EMEA.xlsx"

So, then what should my VLOOKUP look like including the INDIRECT? I can't make anything of it

 

[James006]

Hi,

As an initial test ...

=VLOOKUP($A6,INDIRECT("'"&C13&"\["&C14&"]"&C3&"'!$A:D"),4,0)

Hope this will help

 

[jessica_p]

Hi James,

Thank you for the swift reply. This formula only looks at the current tab, when the cels searching for in the INDIRECT are on a different tab "Parameters"...

 

[James006]

Hi again,

You could test following :

=VLOOKUP($A6,INDIRECT("'"&"Parameters!C13"&"\["&"Parameters!C14"&"]"&"Parameters!C3"&"'!$A:D"),4,0)

Hope this will help