CODEWITHSUNDEEP
google-sheetsspreadsheetarray-formulas
AdOne
AdOne

Reputation: 131

TEXTJOIN with ARRAYFORMULA Google Sheets

I have a little problem getting the array from the function below. When the criterion of the IF function is range (At the site of B11) it stops working and returns just one not conected value. How to get the array result?

=ARRAYFORMULA(TEXTJOIN(" ", TRUE,ARRAYFORMULA(IF('Sheet1'!$B$2:$B15 = B11, 'Sheet1'!$D$2:$D15, ""))))

LINK to Sheet https://docs.google.com/spreadsheets/d/1QQGcPKI895NpwM-9oXccs8Ln4RWXAPaqVq5IBKllKFE/edit?usp=sharing

Upvotes: 1

Views: 882

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&"", 
 TRIM(SPLIT(REGEXREPLACE(TRIM(FLATTEN(QUERY(QUERY({J2:J&"♥", L2:L&","}, 
 "select max(Col2) 
  where Col2 <> ',' 
  group by Col2 
  pivot Col1"),,9^9))), ",$", ), "♥")), 2, 0)))

enter image description here

Upvotes: 2

Related Questions