Find multiple matches in a two-dimensional array in Google Sheets

user_12354523 0
Viewed 49

I'm struggling to find a solution on my own and I couldn't find a relevant answer for my specific case (or a similar one) so here I am. Before anything else I want to say thanks in advance for any help.

Here's my problem:

I have a Google Sheet with a table as follows:

email_address1@domain.com  |  group_address1@domain.com  |  group_address2@domain.com  |  group_address2@domain.com
email_address2@domain.com  |  group_address2@domain.com  |  group_address3@domain.com  |  group_address4@domain.com
...

So, basically it's a list of users in the first column and the groups that each user is a part of in the next columns. Each user is in more than one group.

Now I need to check which users are in each group (instead of checking in which groups each user is). I can easily flatten the range of groups and get the list of unique groups, but now I'm struggling to find a way to query the original table in order to get something like this:

group_address1@domain.com  |  email_address1@domain.com  |  email_address2@domain.com  |  emai3_address1@domain.com
group_address2@domain.com  |  email_address2@domain.com  |  email_address5@domain.com  |  emai3_address6@domain.com

Again, thanks for your help.

google-sheets
2 Answers

try:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(A1:A9&"×"&B1:D9), "×"), 
 "select max(Col1) where Col2 is not null group by Col1 pivot Col2"),,9^9)), " "))

enter image description here

user_56326290

Finally I tried a different approach:

I joined all the group names in a helper column (Column U) and then used the following formula:

=TRANSPOSE(QUERY('Domain 2'!$E$90:$U$167,"select E where U contains '"&L67&"'"))
user_123545230
Related