Vlookup() inside indirect()

[Michaud79]

I’m having trouble setting up a VLOOKUP formula that references another file when inside an INDIRECT function.

Here is an example:

Works fine:

A1: January /// B1: VLOOKUP(C3, ‘file:///C:/File.ods’#$January.C$1:E$10, 3, 0)

A2: February /// B2: VLOOKUP(C3, ‘file:///C:/File.ods’#$February.C$1:E$10, 3, 0)

A3: March /// B3: VLOOKUP(C3, ‘file:///C:/File.ods’#$March.C$1:E$10, 3, 0)

Doesn’t work

A1: January /// B1: INDIRECT(“VLOOKUP(C3, ‘file:///C:/File.ods’#$”&A1&".C$1:E$10, 3, 0)")

A2: February /// B2: INDIRECT(“VLOOKUP(C3, ‘file:///C:/File.ods’#$”&A2&".C$1:E$10, 3, 0)")

A3: March /// B3: INDIRECT(“VLOOKUP(C3, ‘file:///C:/File.ods’#$”&A3&".C$1:E$10, 3, 0)")

Any help would be greatly appreciated, thanks!

P.S.: Please note, C3 is a text value (such as someone’s name) to be searched in the array.

[librebel]

Hello @Michaud79,

The INDIRECT function requires a cell address string in the form “A1”.

If your VLOOKUP function returns a cell address string in the form “A1”, then you can omit the double quotation marks around it.

For further help please state the desired outcome of your formula.

[jimk]

INDIRECT should be inside VLOOKUP, not the other way around. So for B1:

=VLOOKUP($C$3; INDIRECT("'file:///C:/File.ods'#$"&A1&".C$1:E$10"); 3; 0)