'Return multiple results from VLOOKUP
I have a sheet (example here: Demo Sheet)
Sheet 1: contains a list of users with their email address and name.
Sheet 2: contains a list of and the groups that they are a member of.
Sheet 3: I want to 'flip' sheet 2, so rather than have the user and each group, I want the group with each member listed.
I have tried =TRANSPOSE(FILTER(Sheet2!A2:A11,Sheet2!B2:E11=A2))
But Filters need to be a single column, I tried to flatten and get the unique values and use that but it came back with a mismatched quantity.
Anyone have any clue how to do this?
Solution 1:[1]
Here's one way you can do it:
=index(split(trim(transpose(query(if(regexmatch(
substitute(substitute(trim(transpose(query(transpose(
substitute(Sheet2!B2:E," ","??")),,9^9)))," ",","),"??"," "),
transpose(filter(A2:A,A2:A<>""))),Sheet2!A2:A,),,9^9)))," "))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | ztiaa |