Analistable

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.
Customers with every order listed
Customer IDNameOrders
C-101Bakery LuneO-9001, O-9003
C-102Hart & Co
C-103Nordic SupplyO-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))
Open orders per customer, newest first
Customer IDOpen orders
C-101O-9007, O-9003
C-103O-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

  1. Format both ranges as tables (Ctrl+T) and name them Orders and Customers in Table Design → Table Name.
  2. In the Customers table, add a column header such as Orders.
  3. In the first cell of that column, type the TEXTJOIN formula using [@[Customer ID]] instead of A2 so it fills the whole column automatically.
  4. Check a customer by hand: filter Orders for that Customer ID and compare the visible Order IDs with the list.
  5. 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:

Products
SKUProductSupplier
P-01Oat flourMillhouse
P-02Rye flourMillhouse
P-03Sea saltCoastline
P-04Spelt flourMillhouse
Products:  =TEXTJOIN(", ", TRUE, SORT(FILTER(Products[Product], Products[Supplier]=A2, "")))
Count:     =COUNTIF(Products[Supplier], A2)
Result
SupplierProductsCount
MillhouseOat flour, Rye flour, Spelt flour3
CoastlineSea salt1

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

TEXTJOIN and FILTER problems
SymptomCauseFix
#NAME?FILTER or TEXTJOIN not available in your Excel versionUse the IF array version (Excel 2019) or Power Query (Excel 2016)
#CALC!No matching rows and no third FILTER argumentAdd `""` as FILTER's third argument
Empty cell although matches existKey differs by spaces or number vs textCompare with `=TRIM()` on both sides, or convert with `VALUE`/`TEXT`
Duplicates in the listThe same value appears on several matching rowsWrap FILTER in UNIQUE
Same result in every rowLookup 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:

  1. Load Orders into Power Query (Data → From Table/Range).
  2. Choose Home → Group By, group by Customer ID, operation All Rows, new column name Orders.
  3. In the formula bar, replace the All Rows expression each _ with each Text.Combine([Order ID], ", ").
  4. 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.

Related guides