Analistable

How to return every match in one cell with TEXTJOIN

By the Analistable team · Updated · 2 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.

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.

Related guides