Look up required

First off, its my first post so hi and thanks in advance

I have an excel spreadsheet to consolidate accounts. So i have Account types in column a 1 to a 50 (the likes of fixed assets, current assets and the various sub headings) in the current assets and current liabilities are all the inter company accounts so there is a list say from a20 to a30 owed by co x owed by co y etc and from a40 to a50 owed to co x owed to co y etc.

the various co's are then across the top col b is co 1, col c is co 2 etc.

What i want is a formula that matches up all the inter co balances so i can reconcile them.

So i want it to say for Co 1 to pull all the amounts it is owed by other companies (which is easy just col b20 to b30) but also to goto col c-x40-50 and to pull the balance that the other co reports as owing to co 1.

can anyone help

see rough attached example of what info i have sheet 1 and what i want to achieve, sheet 2

thanks a million

can i just create a formula like that in the spreadsheet i need it in or do i need to do the vb stuff?

i cannot seem to replicate that function in another work book

could you give me the steps to adding the code?

thank you