Function to mapping data

[SamuelXiao]

Hi all,

I am struggling for an excel data combination. Hope someone can help
me.
Below col A and Col B is a pair while col C and col D is another pair,
originally col E, F and G are empty.
Then in col E, col F , col G, they copy Ax+Cx(eliminate duplicate),
Bx, Dx respectively.
A B C D E F G
C1 AA C1 111 C1 AA 111
C2 BB C3 222 C2 BB
C3 CC D1 333 C3 CC 222
D1 DD D2 444 D1 DD 333
D3 EE D3 523 D2 444
D4 SS D6 321 D3 EE 523
E4 345 D4 SS
D6 321
E4 345

From the above sample, col A & col C are copied to E1(eliminate
duplicate) and then col Bx copied to Fx(e..g col A1 is "C1", B1 is
"AA", so "AA" copied to F1, then in col C1, it is "C1" which can be
found in col A's range, so Dx is copied to G).
In case there is found only one cell having mapping value (col B or
col D) ,e.g. "C2" can be found in col A but not in col C, in col F,
will just copy the col B's mapping value("BB") and col G left the
field empty.

Do anyone know any functions can do that? I currently am using
"VLOOKUP" but it is not efficient as it can only found either col A's
value in col C or vice versa but not both.

Any help would be appreciated.