Forum Discussion
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...
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.
There are minor differences between the PQ solutions.
And I have corrected the dynamic array solution!
- 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!
