How to Find Matching Data from 1 Column against Multiple Columns Without Losing Data?

[JJL4677]

Help please!
What is the best, quickest and simplest method to Find Matching Data from 1 Column against Multiple Columns Without Losing Data?

I have a column of [default_emails] on one Worksheet1; And three(3) different columns of [email_1], [email_2] and [email_3] on a different Worksheet2.

How can I search the three(3) columns of [email_1], [email_2] and [email_3] all at once against the [default_email] in order to return matches with the [default_email] without losing any data? I also need to pull those that were not-matched, and organized them separately.

Worksheet1

Default_Email

Worksheet2

Client Name

Address

Email_1

Email_2

Email_3

ALSO, I would like to have this as a Table format so that the data can continue to be added/updated to the file/worksheet.

Lastly, I would also like to see if Power Query can be utilized as well. And, Pivot Table to report 'matched' and 'non-matched' groups of data.

Any help would be greatly appreciated!

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[JJL4677]

Thanks for your guidance. File has been uploaded!

[JeteMc]

I don't believe that setting the results up as a table will help, however setting up the data on Worsheets 1 and 2 would.
A helper column (B) is added to Worksheet 1 and is populated using: =INDEX(Worksheet2!A$2:A$35,AGGREGATE(15,6,(ROW(Worksheet2!A$2:A$35)-ROW(Worksheet2!A$1))/(Worksheet2!C$2:E$32=A2),1))
Note that the helper column may be moved and/or hidden for aesthetic purposes.
On the Result Display page column A is populated using: =IF(COUNTA(Worksheet2!A$2:A$32)< ROWS(A$1:A1),"",Worksheet2!A2)
Columns B:E are populated using: =IF($A4="","",INDEX(Worksheet2!B$2:B$32,MATCH($A4,Worksheet2!$A$2:$A$32,0)))
Note that the above results in some cells having a value of zero which is hidden using data validation Cell Value = 0 > Font = White
Column F is populated using: =IFERROR(IF($A4="","",INDEX(Worksheet1!A$2:A$19,MATCH($A4,Worksheet1!B$2:B$19,0))),"")
A new column (G) will display TRUE if there is a match and FALSE if not and is populated using: =F4<>""
The Results can be displayed based on filtering column G to either TRUE or FALSE.
Let us know if you have any questions.

[JJL4677]

I'll check this out and let you know. Thanks!