TEXTJOIN with a delimiter and a condition
TEXTJOIN glues many cells into one piece of text with a separator you choose, and with FILTER inside it joins only the rows that meet a condition.
=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")The sheet: Tasks sheet: column A is Project, column B is Owner. Cell E2 holds a project name, and you want every owner on that project in one cell.
How it works, step by step
- 01FILTER($B$2:$B$100,$A$2:$A$100=E2) returns the owners on rows where the project equals the value in E2.
- 02TEXTJOIN(", ",TRUE,...) joins those owners into one text string, putting a comma and a space between them.
- 03The TRUE in the middle tells TEXTJOIN to skip empty cells, so you do not get stray separators.
- 04IFERROR returns an empty string when FILTER finds no rows, instead of showing #N/A.
The same logic in four tools
=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")-- PostgreSQL. MySQL uses GROUP_CONCAT(owner ORDER BY task_id SEPARATOR ', ').
SELECT project,
STRING_AGG(owner, ', ' ORDER BY task_id) AS owners
FROM tasks
WHERE owner IS NOT NULL
GROUP BY project;import pandas as pd
owners = (
tasks.dropna(subset=["owner"])
.groupby("project")["owner"]
.agg(", ".join)
)Mistakes that break it
- !TEXTJOIN needs Excel 2019 or Microsoft 365. FILTER inside it needs Excel 2021 or 365. In Excel 2019 use TEXTJOIN(", ",TRUE,IF($A$2:$A$100=E2,$B$2:$B$100,"")) and confirm with Ctrl+Shift+Enter.
- !A single cell holds at most 32,767 characters. A very long list is cut off or returns #VALUE!.
- !Passing FALSE as the second argument keeps blank cells, which adds empty items such as "Ana, , Ben".
- !SQL returns rows in no guaranteed order unless you add ORDER BY inside STRING_AGG. Without it the owners may not appear in sheet order.
Common questions
- What is the difference between TEXTJOIN and CONCAT?
- CONCAT joins values with nothing between them. TEXTJOIN adds a delimiter you choose between every value and can skip empty cells.
- How do I join values with a line break?
- Use CHAR(10) as the delimiter, =TEXTJOIN(CHAR(10),TRUE,B2:B6), and turn on Wrap Text for the cell.
- What is the SQL version of TEXTJOIN?
- STRING_AGG(column, ', ') with GROUP BY in PostgreSQL and SQL Server, and GROUP_CONCAT in MySQL and SQLite.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.