Search Multiple Columns all at once to Find, Locate and return Matching Data Details (Power Query)?

[JJL4677]

Help please! 

What is the best, quickest and simplest method?

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 maintain 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!

*I have attached a sample contact list (fake data) with further detail how I would like to see the results. May I please ask if I can get further solution to the attached? Thanks!

[#2]

@JJL4677 

Power Query could be as attached - unpivot all columns with emails in second sheet and inner join  with first one to have only matched emails.

As for PivotTable I didn't catch what exactly would you like to have.

[#3]

@JJL4677 

I can't open @SergeiBaklan 's solution until I put my own workbooks down, but I would argue that the solution lies in unpivoting the 3 email columns.  That goes for both formulae and PQ.

Using Excel 365

= LET(
   k, SEQUENCE(3*ROWS(emails), 1, 0),
   listEmail, INDEX(emails, 1+QUOTIENT(k,3), 1+MOD(k,3)),
   listName, INDEX(Client[Name], 1+MOD(k,3)),
   XLOOKUP(Default[emails], listEmail, listName, "-" ) )

returns client names for each default email.  Filtering the result

= LET(
   k, SEQUENCE(3*ROWS(emails), 1, 0),
   listEmail, INDEX(emails, 1+QUOTIENT(k,3), 1+MOD(k,3)),
   listName, INDEX(Client[Name], 1+MOD(k,3)),
   missing, ISERROR(XMATCH(Default[emails], listEmail) ),
   FILTER(Default[emails], missing ) )

would return a list of orphaned default emails.  This would be somewhat akin to an antijoin using PQ.

[#4]

@PeterBartholomew1 

There are minor differences between the PQ solutions.

And I have corrected the dynamic array solution!

[#5]

@JJL4677 

Thanks, with sample file it's more clear. You may simply add one more column to Names table, entire script is

let
  Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
  #"Add Default Email" = Table.AddColumn(
    Source,
      "Default Email",
      each
        try List.Intersect(
              {
                {[Email_1],[Email_2],[Email_3]},
                DefaultEmail[Default Email]
              }
            ){0}
        otherwise null
    )
in
    #"Add Default Email"

[#6]

Hi Sergei, thanks for your help on this. Would you please guide me on how this script can be added into a workbook that already contains other historical command scripts? (i noticed on my actual file there are other edited script content I had done to clean up the original table). Your kind help would be greatly appreciated. Thanks!

[#7]

Hi Peter, I've attached a file in my original message to clarify how I would like the results to be displayed. If you had the chance to review, that would be great!

[#8]

@JJL4677 

To copy/paste queries

- open file attached to my post ("Source") and your file ("Target")

- with Data->Queries and Connections open right pane in both file

- in pane of Target file right click on any empty space and create New Group, let say Dev

- right click on Names query in Source and click Copy

In target click on Dev group, right click on it and Paste

- queries shall appear here, possibly you will see an error that table Names not found

that's okay, click Ok, double click on query and on first step change in formula bar on your actual table name

- same with second query if necessary

- query Names will be loaded into new sheet. You may move the table, or right click on query, Load To as connection only, delete created sheet and Load again query to the Table.

You may do the same without creating new group, but pasted queries will be among you old ones and it will be harder to find them. After everything works you may move queries to another group or to the root.

[#9]

Hi Sergei, I just tried your script, and it worked!!! but, have a small problem.
It appears that if there are multiple matches, it seems to pick up and return only one(1) of them. Is there a way the Default Email column can return multiple matching emails, if any, by semi-colon, so that all matching emails from each row is included in that one column [Default Email]?

[#10]

@JJL4677 

When instead of returning first element (assuming it's only one) we return all elements combined as text

let
  Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
  #"Add Default Email" = Table.AddColumn(
    Source,
      "Default Email",
      each
        Text.Combine(
          try List.Intersect(
            {
              {[Email_1],[Email_2],[Email_3]},
              DefaultEmail[Default Email]
            })
          otherwise null,
          "; "
        )
    )
in
    #"Add Default Email"