Align filtered results in Google Sheets

Question

Sheet 1, Sheet 2 & Sheet 3 are in a single file.

Sheet 1 (In order) [large list]

Name  Email
A     A@xyz //means A's email is A@xyz
B     B@xyz
C     C@xyz
D     D@xyz
E     E@xyz
...
...

Sheet 2 (In order) [large list]

Name  Email
F     F@xyz
G     G@xyz //means G's email is G@xyz
H     H@xyz
...
...

Sheet 3 (Out of order) [smaller list, filtered (from another source)]

Name  Email
B     A@xyz
A     D@xyz
D     B@xyz
F     H@xyz
G     F@xyz
...
...

I'm expecting Sheet 3 to be like:

Name  Email
B     B@xyz
A     A@xyz
D     D@xyz
F     F@xyz
G     G@xyz
...
...

How do I map names to correct email-ids in Sheet 3?

Note: There's no such case that a name is present but corresponding email-id is not and vice-versa.

Answer 1

Well since basically you got 2 sources of data then, try in Sheet3!B2:

=ARRAYFORMULA(IF(LEN(A2:A),IFERROR(VLOOKUP(A2:A,Sheet1!A:B,2,0),VLOOKUP(A2:A,Sheet2!A:B,2,0)),))