CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
SL8t7
SL8t7

Reputation: 647

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?

Upvotes: 0

Views: 113

Answers (1)

z..
z..

Reputation: 12823

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)))," "))

enter image description here

Upvotes: 2

Related Questions