Ask Your Question

vlookup() inside indirect()

asked 2018-01-22 08:06:57 +0200

Michaud79 gravatar image

updated 2018-01-22 08:08:37 +0200

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.

edit retag flag offensive close merge delete


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.

librebel gravatar imagelibrebel ( 2018-01-22 08:23:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-22 22:09:57 +0200

Jim K gravatar image

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)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-22 08:06:57 +0200

Seen: 58 times

Last updated: Jan 22 '18