Vlookup depending on cell value

[p9j123]

Need help on the following scenarion

I have a masterfile that looks like this.

[TABLE="class: grid, width: 285, align: left"]
<colgroup><col><col span="2"><col></colgroup><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

=VLOOKUP(A2,'C:\Source\[Michael.xlsx]Sheet1'!$A:$D,4,FALSE)

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.

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

 

[HackSlash]

All referenced files must be open in order to read their values. The values will appear as last seen until the workbooks are opened again.