Build Database - 1 master files with several files (VLOOKUP)

Hi there,

I confess I have just registered for help! Nonetheless, I have used this forum several times looking for vba code and excel tips.

My conundrum is as follows:

I would like to build a MasterFile, where I can aggregate the Production of each country (~230) per product (~120).

So, the objective is to fill the Master file (Countries in rows, Products in collumns) with information from each file (~120 = a file for each product) in the same folder, as show in the attached MasterDB file.

The other small caveat is that the number of the collumn where the data should be matched with the country and pasted, is in cell A2 of each file.

Example: "Product : 4407 Wood"

But for this I already have the solution in a formula (that I suppose can become a variable?)

=VALUE(MID(MID(MID(SUBSTITUTE(A2;" ";"^";2);1;256);FIND("^";SUBSTITUTE(A2;" ";"^";2));256);2;FIND(" ";MID(MID(SUBSTITUTE(A2;" ";"^";2);1;256);FIND("^";SUBSTITUTE(A2;" ";"^";2));256))-2))

I'm sending 3 files so that it's better to understand:

MasterDB - Where info should be compiled
4401 & 4407 - Files with info

Well, hope anyone can take on this challenge!

Many thanks,