Apologies if it is unclear.

Assuming that the Neighbourhood, LSOA, Weightings table is on Sheet1
starting in Cell A1 and that the LSOA, Job Count table is on Sheet2
starting in Cell A1.

The basic formula is Weighting of LSOA in Neighbourhood / 100 * LSOA
job Count + Weighting of LSOA in Neighbourhood / 100 * LSOA job Count
etc

To calculate the job count for Area 1, 2 and 3 I would use the
following formulas

Area1 =Sheet1!C2/100*Sheet2!B2+Sheet1!C3/100*Sheet2!B3+Sheet1!
C4/100*Sheet2!B4+Sheet1!C5/100*B5
Area2 =Sheet1!C6/100*Sheet2!B3+Sheet1!C7/100*Sheet2!B6+Sheet1!
C8/100*Sheet2!B7+Sheet1!C9/100*Sheet2!B8+Sheet1!C10/100*Sheet2!
B9+Sheet1!C11/100*Sheet2!B10
Area3 =Sheet1!C12/100*Sheet2!B3+Sheet1!C13/100*Sheet2!B2+Sheet1!
C14/100*Sheet2!B11

I thought this could be automated by concatenating columns A and B on
Sheet1 and performing a lookup on them. However I expereinced
difficulties when trying to configure what value to lookup. I thought
I would have to have a list of every possible combination of
Neighbourhood and LSOA on Sheet2 then check to see which ones existed
on Sheet1 then draw another lookup to bring that value into the
calculation. Which seems to over complicated to me.

So from what you have suggested Arvi this can be done by use of a
pivot table?

Thanks,

Clive