Vlookup depending on cell value

[p9j123]

Need help on the following scenarion

I have a masterfile that looks like this.

Task ID	Team	Ops	Status
123	A	Michael	Completed1
456	C	Jude
256	F	Michael
933	G	John
858	J	Jude
451	I	Michael

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

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.