The easy solution is to use SQL. The first problem is that you have 2 data sources (workbooks).

1. Use Excel as an automation server, open each workbook and save the relevant sheet in each in CSV format (if it is a one off, you can do this manually)
2. Use the ODBC text driver to read each CSV and to join them
3. Write the resulting recordset to a CSV or write it directly to a workbook. (lookup CopyFromREcordset in the Excel VBA help file)

On 2: (this is the (untested) code you want

Cnn ="Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\;
Sql = "SELECT * FROM FIRST.CSV a,SECOND.CSV b WHERE a.[Cost centre]=b.[Cost Centre]
Set RS = CreateObject("ADODB.Recordset"
RS.Open Sql, Cn