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

Question

Help please!&nbsp;What is the best, quickest and simplest method?&nbsp;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.&nbsp;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]&nbsp;without losing any data? I also need to pull those that were not-matched, and organized them separately.&nbsp;Worksheet1Default Email&nbsp;Worksheet2&nbsp;&nbsp;&nbsp;&nbsp;Client NameAddressEmail_1Email_2Email_3&nbsp;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.&nbsp;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.&nbsp;Any help would be greatly appreciated!&nbsp;*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!

sergeibaklan · Answer

JJL4677&nbsp;
Power Query could be as attached - unpivot all columns with emails in second sheet and inner join&nbsp; with first one to have only matched emails.
&nbsp;
As for PivotTable I didn't catch what exactly would you like to have.

peterbartholomew1 · Answer

JJL4677&nbsp;I can't open&nbsp;SergeiBaklan&nbsp;'s solution until I put my own workbooks down, but I would argue that the solution lies in unpivoting the 3 email columns.&nbsp; 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.&nbsp; 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.&nbsp; This would be somewhat akin to an antijoin using PQ.

peterbartholomew1 · Answer

PeterBartholomew1&nbsp;There are minor differences between the PQ solutions.And I have corrected the dynamic array solution!

sergeibaklan · Answer

JJL4677&nbsp;
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"
&nbsp;

jjl4677 · Answer

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!

Forum Discussion

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

Share

Resources

Worksheet2
Client Name	Address	Email_1	Email_2	Email_3