0

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.

4
  • Welcome. How do I align the names and email-ids in Sheet 3? It's not clear what you mean by "align" the names and email-ids. Would you please be more specific, and also provide an example of how a successful outcome would appear. I note that "A" (Sheet 1) email = "A@xyz" but the email on Sheet3 = "D@xyz"; presumably this is what you are driving at, but it is still unclear what outcome you want.
    – Tedinoz
    Dec 9 '20 at 11:53
  • Thanks for bringing that up, I've edited the post, hope it's clearer now. Dec 9 '20 at 12:06
  • So, do you have two worksheets with Names and Emails as a source instead of one?
    – JvdV
    Dec 9 '20 at 12:37
  • All of them (sheet 1, 2 & 3) are in the same spreadsheet (one file). The filtered sheet 3 is a bit lo..ong for me to describe (and that process messed up the order). But I don't mind having two other columns in sheet 3 that maps names to correct emails... which can be logically derived from sheet 1 & sheet 2... but I'm having trouble finding so.. Dec 9 '20 at 12:49
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)),))
7
  • Thanks, I tired but I get Function VLOOKUP parameter 3 value is 0. It should be greater than or equal to 1. | #VALUE in certain cells, but I'm sure that all the names & email-ids in Sheet 3 exists either in Sheet 1 or in Sheet 2. Dec 10 '20 at 11:55
  • Also, it populates two columns of email-ids, but the positions are correct when compared with the name list in Sheet 3. Dec 10 '20 at 11:57
  • @JovialJoeJayarson, it will be halpfull if you can share a copy of your sheet in your original posting
    – JvdV
    Dec 10 '20 at 12:02
  • I'm not permitted to, I'm sorry. let me see if I can create a demo... Dec 10 '20 at 12:03
  • thanks a lot! Got it, there was an error from my part in the formula.. :facepalm: Dec 10 '20 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.