How to return every match in one cell with TEXTJOIN
By the Analistable team · Updated · 5 min read
Lookups return the first match only. To list every matching value in one cell, use =TEXTJOIN(", ", TRUE, FILTER(Orders[Order ID], Orders[Customer ID]=A2, "")). It works in Microsoft 365, Excel 2021 and Google Sheets. In Excel 2019, use TEXTJOIN with an IF array instead of FILTER.
Part of our guide: How to join spreadsheets on a common column
The problem
A customer has three orders. =XLOOKUP("C-101", Orders[Customer ID], Orders[Order ID]) returns O-9001 and stops. Often you want the whole list next to the customer: “O-9001, O-9003, O-9007”.
The formula
=TEXTJOIN(", ", TRUE, FILTER(Orders[Order ID], Orders[Customer ID]=A2, ""))FILTER(...)returns every Order ID whose Customer ID equals A2.TEXTJOIN(", ", TRUE, ...)joins them with a comma and space, skipping blanks.- The
""at the end of FILTER returns an empty string instead of #CALC! when there are no matches.
| Customer ID | Name | Orders |
|---|---|---|
| C-101 | Bakery Lune | O-9001, O-9003 |
| C-102 | Hart & Co | |
| C-103 | Nordic Supply | O-9002 |
Variations
- Unique values only:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Orders[Product], Orders[Customer ID]=A2, ""))). - Sorted: wrap the FILTER in SORT.
- Two conditions:
FILTER(Orders[Order ID], (Orders[Customer ID]=A2) * (Orders[Status]="Open"), ""). - Line breaks instead of commas: use
CHAR(10)as the delimiter and turn on Wrap Text.
Excel 2019 and Google Sheets
Excel 2019 has TEXTJOIN but not FILTER. Use IF inside TEXTJOIN, confirmed with Ctrl+Shift+Enter:
=TEXTJOIN(", ", TRUE, IF(Orders!$B$2:$B$500=A2, Orders!$A$2:$A$500, ""))Google Sheets supports the FILTER version as written. Excel 2016 and earlier have no TEXTJOIN; use Power Query Group By with Text.Combine, as shown in combining duplicate rows.
Excel cells hold at most 32,767 characters, and TEXTJOIN returns #VALUE! beyond that. For long lists, a join that returns one row per match is easier to work with — see joining two tables in Excel.
Worked example with two conditions and sorting
List the open orders of each customer, newest first:
=TEXTJOIN(", ", TRUE,
SORTBY(
FILTER(Orders[Order ID], (Orders[Customer ID]=A2) * (Orders[Status]="Open"), ""),
FILTER(Orders[Date], (Orders[Customer ID]=A2) * (Orders[Status]="Open"), ""),
-1))| Customer ID | Open orders |
|---|---|
| C-101 | O-9007, O-9003 |
| C-103 | O-9002 |
SORTBY sorts the order IDs by their dates; -1 means descending. Both FILTERs must use the same conditions so the two arrays line up.
TEXTJOIN or a join?
TEXTJOIN is ideal for display: one row per customer with a readable list. For analysis — counting, summing or filtering the matched rows — keep them as rows. A join in Power Query returns one row per order with the customer's details attached.
Step by step in Excel 365
- Format both ranges as tables (Ctrl+T) and name them Orders and Customers in Table Design → Table Name.
- In the Customers table, add a column header such as Orders.
- In the first cell of that column, type the TEXTJOIN formula using
[@[Customer ID]]instead of A2 so it fills the whole column automatically. - Check a customer by hand: filter Orders for that Customer ID and compare the visible Order IDs with the list.
- If you need the list as static text for sending to someone, copy the column and Paste Special → Values.
=TEXTJOIN(", ", TRUE, FILTER(Orders[Order ID], Orders[Customer ID]=[@[Customer ID]], ""))Second example: products per supplier with counts
A purchasing table lists one row per product with its supplier. You want each supplier's products in one cell and the number of products beside it:
| SKU | Product | Supplier |
|---|---|---|
| P-01 | Oat flour | Millhouse |
| P-02 | Rye flour | Millhouse |
| P-03 | Sea salt | Coastline |
| P-04 | Spelt flour | Millhouse |
Products: =TEXTJOIN(", ", TRUE, SORT(FILTER(Products[Product], Products[Supplier]=A2, "")))
Count: =COUNTIF(Products[Supplier], A2)| Supplier | Products | Count |
|---|---|---|
| Millhouse | Oat flour, Rye flour, Spelt flour | 3 |
| Coastline | Sea salt | 1 |
To build the supplier list itself, use =UNIQUE(Products[Supplier]) in A2 and refer to the spill as A2#; the TEXTJOIN formula then needs one copy per row, or a MAP over A2# in Microsoft 365.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| #NAME? | FILTER or TEXTJOIN not available in your Excel version | Use the IF array version (Excel 2019) or Power Query (Excel 2016) |
| #CALC! | No matching rows and no third FILTER argument | Add `""` as FILTER's third argument |
| Empty cell although matches exist | Key differs by spaces or number vs text | Compare with `=TRIM()` on both sides, or convert with `VALUE`/`TEXT` |
| Duplicates in the list | The same value appears on several matching rows | Wrap FILTER in UNIQUE |
| Same result in every row | Lookup cell not relative (for example `$A$2`) | Use A2 or a structured `[@column]` reference |
Splitting the list back into rows
If someone sends you a sheet with comma-separated lists and you need rows again, Microsoft 365 has TEXTSPLIT: =TEXTSPLIT(B2, , ", ") spills the values down a column. In Google Sheets, =TRANSPOSE(SPLIT(B2, ", ", FALSE)) does the same; the FALSE stops SPLIT treating each character of the delimiter separately. In Power Query, Split Column → By Delimiter → Advanced options → Rows handles a whole column at once.
Power Query alternative for large tables
Thousands of FILTER formulas over a long Orders table can slow a workbook down, because each one scans the whole table. Power Query builds the lists once per refresh:
- Load Orders into Power Query (Data → From Table/Range).
- Choose Home → Group By, group by Customer ID, operation All Rows, new column name Orders.
- In the formula bar, replace the All Rows expression
each _witheach Text.Combine([Order ID], ", "). - Close & Load, then join the result to Customers on Customer ID if you need names alongside.
Text.Combine expects text, so convert numeric IDs with Transform → Data Type → Text before grouping. The same pattern is used to merge repeated rows in combining duplicate rows in Excel.
Frequently asked questions
- How do I return multiple matches in one cell in Excel?
- Use =TEXTJOIN(", ", TRUE, FILTER(return_range, lookup_range=value, "")) in Microsoft 365 or Excel 2021.
- Does this work in Google Sheets?
- Yes. Google Sheets supports TEXTJOIN and FILTER with the same arguments.
- What if I have Excel 2016?
- Excel 2016 has no TEXTJOIN. Use Power Query Group By with Text.Combine, or a helper column approach.
- How do I put each match on a new line in the same cell?
- Use CHAR(10) as the TEXTJOIN delimiter in Excel (CHAR(10) also works in Google Sheets) and turn on Wrap Text for the column.