Help on Indirect

[p9j123]

Need help on the following scenario.


I am trying to use the below formula but I cant make it work.

=VLOOKUP(A2,INDIRECT("'C:\Source\["&C2&".xlsx]Sheet1"&"'!"&"$A:$D"),4,0)

I have a masterfile that looks like this.

[TABLE="class: cms_table_grid, width: 285, align: left"]
<tbody>[TR]
[TD]Task ID[/TD]
[TD]Team[/TD]
[TD]Ops[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]A[/TD]
[TD]Michael[/TD]
[TD]Completed1[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]C[/TD]
[TD]Jude[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]256[/TD]
[TD]F[/TD]
[TD]Michael[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]933[/TD]
[TD]G[/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]858[/TD]
[TD]J[/TD]
[TD]Jude[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]451[/TD]
[TD]I[/TD]
[TD]Michael[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Column D has the following formula

I have multiple files on C:\Source that correspond to the names on Column C.

The formula should be dependent on value on column C, on the example above D2 lookup for the value on Michael.xlsx because C2 is Michael. So D3 should look for Jude.xlsx and so on

Note: Source File are closed (im not sure if this info matters.)

 

[sheetspread]

It has to be open for indirect

 

[p9j123]

Oh, is there any substitute formula?

 

[sheetspread]

Try this:

https://www.mrexcel.com/forum/excel...native-vlookup-indirect-closed-workbooks.html

 

[p9j123]

Thank you but not exactly what I need.