How to return all matching values without duplicates into one cell in Google Sheet?

Question

I'm stuck trying to vlookup multiples values duplicates and return all match into one cell.

I would like to convert with formulas a sheet like this:

Name	Product
James	Peach
James	Apple
James	Cherry
Andy	Banana
Wallace	Peach
Wallace	Cherry
Mike	Banana

On a new sheet like this:

Name	Product
James	Peach,Apple,Cherry
Andy	Banana
Wallace	Peach,Cherry
Mike	Banana

Here is an example spreadsheet

Edit: I forgot to specify it, but the result should be in a new tab, like a vlookup

I have not found a functional solution in my research, here is the post that seems to come closest to my need. But the proposed formula does not work: =TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Do you have any idea how I can solve this formula? Your help will be greatly appreciated. I'm going in a loop and I can't find a solution :(

Answer 1

You can use:

=JOIN(", ";FILTER(B:B;A:A=E2;B:B<>""))

Or for the whole column:

=byrow(E2:E;lambda(each;if(each="";"";join(", ";filter(B:B;A:A=each;B:B<>"")))))