Look up value according to Mapping and Sum up values

[Eddie]

I have two reports connected by mapping sheets. I need a macro to
abstract values from report 1 to report 2 according to mapping sheets.
Sometime, the values have to sum up if the particular items is mapping
to same items. In addition, Group A in report 1 location could be
different from Group A location in report 2. I try to use index
function and it didn't work out well. I know this is little
challenge and I have tried many different ways and didn't work out
well. I appreciate any help and any idea about how to do it. Thank you
very much. Please see my little sample data for more information.


Mapping sheets
G1 M1
G2 M2
G3 M3
G4 M3
G5 M4


Report 1
A B C
G1 100 1000 10000
G2 200 2000 20000
G3 300 3000 30000
G4 300 3000 30000
G5 400 4000 40000


Report 2
B A C
M1 1000 100 10000
M2 2000 200 20000
M3 6000 600 60000
M4 4000 400 40000