Pulling Data From Quotes in Other Workbooks

Hello,

I'm working to develop a master Excel sheet to compare data from a folder of workbooks. Using VLOOKUP, I am able to have the master search through a range of cells, find the corresponding data (whether it is material cost, or labor cost for example, by searching for these words), and populate the correct cell in the master workbook.

This works well, however, it requires manually opening the other files and selecting the range for the master to parse. For final use, I want the master to be able to look at each other workbook in the folder, find the data similar to VLOOKUP, and then populate the corresponding cell in the master file.

I've been able to use some VBA to generate a list of cells of every xls in a folder, but running into problems actually using this list with a VLOOKUP. When referenced manually, it seems to automatically add ' and [ and ], as well as a reference to the sheet. How can I go about automating this?

Some assumptions:
1 sheet in every file I am pulling data from
I am pulling the values and creating a table comparing Product Name, Material Cost, and Labor Cost.

Thanks for your update, I've attached a "Master File" example file, as well as a couple dummy bill of materials. It is okay if these would be in the same directory, but being able to have the bill of materials in a separate folder would be preferable.

Please let me know if anything needs clarification.