Compare, match Cells from different worksheets and return result

[johnsending]

Hello Everyone,

I'm seeking a formula that can help me with the following:

Three worksheets and values on Column "A" & "D" on all worksheets exist and do not change. Columns "B" & "C" on "gw1" & "gw2" need input from user.
I need a formula that can search both "gw1" & "gw2" and return a value on cells "B" and "C" on "sw1" after two criterias are met.



Criterias are on Cells B & C on "sw1" where Cell B will be feeding from info provided by Cell C:
Cell C:
If A1 on "sw1" matches any cell in C1:C6 of either "gw1" or "gw2" AND "sw1" matches to any cell in B1:B6 on "gw1" or "gw2", return value from corresponding row in Column "C" from any worksheet it matched from.



Cell B:
If A1 on matches any cell in C1:C6 of either "gw1" or "gw2" AND Cell C1 matches any cell in A1:A6 on "gw1" or "gw2", return value from corresponding row in Column "D" from any worksheet it matched from.


Worksheet1 (gw1):

A B C D
1 g1 sw1 g1.1 gw1
2 g2 sw2 g1.1 gw1
3 g3 sw3 g1.2 gw1
4 g4 sw4 t1.1 gw1
5 g5 sw5 t1.2 gw1
6 g6 sw6 g1.1 gw1


Worksheet2 (gw2):
A B C D
1 g1 sw1 g1.4 gw2
2 g2 sw2 g1.2 gw2
3 g3 sw3 g1.1 gw2
4 g4 sw4 t1.2 gw2
5 g5 sw5 t1.1 gw2
6 g6 sw6 g1.2 gw2


Worksheet3 (sw1):

A B C
1 g1.1 gw1 g1
2 g1.2 N/A N/A
3 g1.3 N/A N/A
4 g1.4 gw2 g1
5 t1.1 N/A N/A
6 t1.2 N/A N/A


I do apologize for the lengthy explanation but this problem has been bugging me some time now and just when I think I got it figured out, something goes wrong. Tried VLOOKUP and INDEX & MATCH but the criteria makes it difficult to return the correct results. I'm hoping a set of expert eyes can assist me with this problem.
Thank you,
John