Forum Discussion

JJL4677's avatar
JJL4677
Copper Contributor
Jan 30, 2021

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

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 dif...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 30, 2021

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.

Resources