Dynamically change name of source data in VLOOKUP

I need help dynamically changing the source of where my data comes from in a VLookup.

I'm working off a document that aggregates data for each month of the year. The data for this comes from several different documents on a monthly basis.

So I need my Vlookup to source from different documents.

So for example, I would have this formula in the September2009 column that needs to reference the September 2009 document.

=VLOOKUP(A5,'SourceData September2009.xls]National - FM'!$B$3:$F$69,2,0)

But the next column needs to source from the October2009 document, so:

=
=VLOOKUP(A5,'SourceData October2009.xls]National - FM'!$B$3:$F$69,2,0)

I feel like this is fairly simple, I just don't know how to do it. Thanks.

Ok, I'm trying the following, but it doesn't work.

=VLOOKUP(A5,INDIRECT('["Indicateurs "&TEXT(B3,)&" "&TEXT(C1,0)&"2009"&".xls"]National - FM'!$B$3:$F$69,),2,0)

The intention is for the document to reference the following document:

Document: Indicateurs PECM September2009.xls (PECM is B3, and September is C1)
Sheet: National - FM

But instead, excel says that it can't find document: Indicateurs "&TEXT(B3,)&" "&TEXT(C1,0)&"2009"&".xls

Which is clearly none existent.

Any thoughts?

That's awesome! Thanks so much.

Question: why does the "PECM" portion (B5) not need to be in Text, but the month does. and why do I need to add "mmmm" in the Text?

This is really helpful, thanks so much.

Thanks so much for the quick and very thorough reply.

This was really really helpful!

Btw, I'm trying to download the .ext plug-in, but I'm using a Excel 2008 for Mac.

Any idea where I can find this plug in?

thanks again!