Compare 4 column values then insert a 5th columns value to a blank column/cell value

Ok, I know the title is a little confusing but I will explain the function that I need to solve my problem completely.
First, there is a sample Workbook attached to this message so you can get a general idea of the layout of the real Workbook.

*** What I need the function to do ***

1) I have a Workbook with 2 sheets.
2) The first worksheet is called EMAIL_old and the second worksheet is called EMAIL_new.
3) Worksheet EMAIL_old (first sheet of the attached example Workbook) has 4 columns named: New Email(A2:A21), Old Email 1(B2:B21), Old Email 2(C2:C21), Old Email 3(D2:D21)
4) Worksheet EMAIL_new (second sheet of the attached example Workbook) has 2 columns: Old Email(A2:A21) and New Email(no cell values yet)
5) I need to search B2:D21 (i.e. all values in Old Email 1, Old Email 2 and Old Email 3) from EMAIL_old and compare the values in all those columns with the value
contained in EMAIL_new column Old Email(A2:A21 of the second sheet)
6) If an email address is found anywhere in Old Email 1, Old Email 2, or Old Email 3 (i.e B:D of first worksheet), then the value of New Email(A2:A21 of the first worksheet)
should be copied into the column New Email of EMAIL_new (B2:B21 of the second worksheet).
7) If no match is found using the above test in Step 6, the value "NO MATCH" should be added to the New Email column (i.e. B2:B21 of the second worksheet)

Examples from included Workbook that is attached to this message:
1) The value of cell A2 (Old Email) in the EMAIL_new worksheet (i.e. the first column of the second worksheet) is [email protected]
a) A search of Old Email 1 (B), Old Email 2 (C) and Old Email 3 (D) in the EMAIL_old worksheet (i.e. the 2nd,3rd and 4th columns of the first worksheet)
shows that the value of cell B16 is [email protected]
b) So that is a match as cell B16 (column Old Email 1) of EMAIL_old worksheet (i.e. the first worksheet) is
equal to the value that is in cell A2 (Old Email) of EMAIL_new (i.e the second worksheet)
c) So the value contained in cell A16 (jason4@company) (column New Email) of EMAIL_old (i.e. the first worksheet)
should be inserted into cell B2 (New Email) of EMAIL_new (i.e. the second worksheet). So A16(sheet EMAIL_old) is [email protected] and B2 (sheet EMAIL_new) is [email protected]
2) The value of cell A8 (Old Email) in the EMAIL_new worksheet (i.e. the first column of the second worksheet) is [email protected]
a) A search of Old Email 1 (B), Old Email 2 (C) and Old Email 3 (D) in the EMAIL_old worksheet (i.e. the 2nd,3rd and 4th columns of the first worksheet)
shows that the value [email protected] is not found anywhere in these columns.
b) So that is NOT a match.
c) So the value of cell B8 (New Email) of EMAIL_new (i.e. the send worksheet) should be set to "NO MATCH"

I hope that I have made sense and that I didn't ramble too much. Feel free to reply to this thread if you need clarification.

this is a bit ugly, but it does the job, based on you'r requirements...

Please Login or Register  to view this content.

ok i think i underatand better now, try this...

Please Login or Register  to view this content.

glad to help, and thanks for the rep