Search code examples
google-sheetsjoingoogle-sheets-formulamatcharray-formulas

Using array formula to join all matches in a cell

I am using google sheets and found a formula that works but I have to drag it down 1000s of rows which is making the sheet super slow/unuseable.

Basically I want to join all matches into a single cell. I am hoping array formula can be used somehow.

ColA Col K
1 Value 1
1 Value 2
1 Value 3
1 Value 4
2 Value 2
2 Value 4
2 Value 1

If cell A24 contains "1" then return Value 1 Value 2 Value 3 Value 4 All in one cell

If cell A24 contains "2" then return Value 2 Value 4 Value 1 All in one cell

Hope I am explaining ok

This is what I have but have to drag... =arrayformula(TEXTJOIN(Char(10), TRUE, IF(IFERROR(MATCH($K$4:$K, IF(A24=$A$4:$A,$K$4:$K,""),0),"")=MATCH(ROW($A$4:$A),ROW($B$4:$B)),$K$4:$K,"")))

Solution

  • You may try:

    =map(A4:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,textjoin(char(10),1,ifna(filter(K:K,A:A=Σ))))))

    enter image description here