List All Matching Values in One Cell

To list matching values in one cell in Excel, use FILTER to find every match and TEXTJOIN to join them into a single text string: =TEXTJOIN(", ",TRUE,FILTER(A2:A8,B2:B8="Sales")). Instead of one result, like VLOOKUP gives, you get "Aisha, Carla, Elena" in one tidy cell. This guide is for anyone building a summary table, a report or a test answer where every match must fit in one place.

The sample data#

All the examples use this staff list in A1:C8:

The question is: who works in Sales? A lookup like VLOOKUP or XLOOKUP stops at the first match and returns only "Aisha". You need all three names.

How to list matching values in one cell with TEXTJOIN and FILTER#

The formula has two parts. FILTER finds the matches, and TEXTJOIN joins them.

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,B2:B8="Sales"))

The result is Aisha, Carla, Elena. Here is what each part does:

• FILTER(A2:A8, B2:B8="Sales") checks each department and returns the names in rows where it is Sales. On its own, it would spill three names into three cells.
• TEXTJOIN(", ", TRUE, ...) takes that list and joins it into one text string. The first argument is the delimiter, the text placed between items. TRUE tells it to skip empty cells.

Both functions need Excel 2021 or Microsoft 365. The FILTER function guide and the TEXTJOIN function guide explain every argument.

How to use a cell as the criteria#

Point the condition at a cell so one formula answers many questions. Type the departments Sales, IT and HR in E2:E4, then in F2:

=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$8,$B$2:$B$8=E2))

Fill it down to F4 (Ctrl+D on Windows, ⌘D on a Mac):

The $ signs lock the data ranges. E2 has no $, so it changes to E3 and E4 as you fill. This is the most useful form of the formula, because the summary table updates when the staff list changes.

How to list matches with two or more conditions#

Inside FILTER, multiply conditions for AND logic and add them for OR logic. Each condition goes in its own brackets.

AND: people in Sales who work in London:

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,(B2:B8="Sales")*(C2:C8="London")))

The result is Aisha, Elena. Carla is in Sales but works in Leeds, so she is left out.

OR: people in Sales or HR:

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,(B2:B8="Sales")+(B2:B8="HR")))

The result is Aisha, Carla, David, Elena, Grace, in the order they appear in the list. Each condition gives TRUE or FALSE for every row. Multiplying keeps a row only when both are TRUE, and adding keeps it when at least one is.

How to remove duplicates and sort the list#

Wrap FILTER in UNIQUE to drop repeats and in SORT to put the results in order. Which offices does the Sales team work in?

=TEXTJOIN(", ",TRUE,SORT(UNIQUE(FILTER(C2:C8,B2:B8="Sales"))))

FILTER returns London, Leeds, London. UNIQUE keeps London and Leeds, and SORT puts them in A to Z order. The result is Leeds, London. Without UNIQUE you would see London twice. To count the distinct items instead of listing them, see how to count unique values.

How to handle no matches and other display options#

If nothing matches, FILTER returns the #CALC! error, and TEXTJOIN passes it on. Give FILTER a third argument, the value to show when it finds nothing:

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,B2:B8="Finance","None"))

The result is None, because no one works in Finance.

You can also change the delimiter to suit the report:

CHAR(10) is a line break. Turn on Wrap Text on the Home tab, or the names run together on one line. If you want text before the list, such as "Team: ", join it with &, as shown in adding text to cells.

A cell can hold up to 32,767 characters. If the joined text would be longer, TEXTJOIN returns #VALUE!. That is rarely a problem for names, but it can happen with long comments.

How to list matches in one cell in older Excel#

In Excel 2019, which has TEXTJOIN but not FILTER, use IF to build the list:

=TEXTJOIN(", ",TRUE,IF(B2:B8="Sales",A2:A8,""))

IF returns the name for Sales rows and an empty string for the others. TEXTJOIN then skips the empty strings because the second argument is TRUE. In Excel 2019, confirm the formula with Ctrl+Shift+Enter so it works as an array formula. In Microsoft 365, plain Enter is enough.

Excel 2016 and earlier don't have TEXTJOIN. There, a PivotTable that lists names under each department is often the simplest option.

When to use one cell and when to spill a list#

One cell is best for summaries, labels and short lists you read at a glance. A spilled list is better when you want to sort, count or use the results in other formulas, because each match sits in its own cell.

Keep in mind that the joined result is text. You can't sum it, and a lookup can't find one name inside it without a wildcard. If the staff list grows, turn it into an Excel table (Ctrl+T on Windows, ⌘T on a Mac) and point the formula at the table columns, so new rows are included without editing the ranges. The guide on returning multiple values with a lookup covers the spilled version, and making a top N list shows how to pick only the highest values.

Practise it#

Practise the FILTER function in a live spreadsheet: you type the formula and it checks it instantly. Then practise joining text to get comfortable with TEXTJOIN, and try the exercise on this page, which lists the Sales team in one cell.