Spreadsheet Practice

How to return multiple matching values in one cell in Excel

Formula (Excel and Google Sheets)

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

Filter the matches and join them: =TEXTJOIN(", ",TRUE,FILTER(results,lookup_range=value)).

Result on the example
Aiko, Chidi, Fay in E2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

VLOOKUP and XLOOKUP stop at the first match. FILTER returns all of them as a list, and TEXTJOIN joins that list into one piece of text with ", " between the items. The TRUE skips empty cells.

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

Result"Aiko, Chidi, Fay"

  1. B2:B87 rows × 1 col: "Aiko", "Ben", "Chidi", "Dana", …

    A range: 7 cells in one column.

  2. A2:A87 rows × 1 col: "Design", "Sales", "Design", "IT", …

    A range: 7 cells in one column.

  3. E1"Design"

    The value in E1.

  4. A2:A8=E1{7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …

    The = operator tests whether the two sides are equal.

  5. FILTER(B2:B8,A2:A8=E1){3 rows × 1 col} "Aiko", "Chidi", "Fay"

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeB2:B87 rows × 1 col: "Aiko", "Ben", "Chidi", "Dana", …
    • condition1A2:A8=E1{7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
  6. TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1))"Aiko, Chidi, Fay"

    TEXTJOIN: Joins text with a delimiter between items.

    • delimiter", "
    • ignore_emptyTRUE
    • text1FILTER(B2:B8,A2:A8=E1){3 rows × 1 col} "Aiko", "Chidi", "Fay"

In Google Sheets

The same formula works in Google Sheets: =TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1)). We ran it in Google Sheets mode on the same data and it returns Aiko, Chidi, Fay.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=FILTER(B2:B8,A2:A8=E1)spills 3 × 1Different resultWithout TEXTJOIN the matches spill down, one per cell. One name per row instead of one cell.

Common mistakes

  • If nothing matches, FILTER returns #CALC!. Add a fallback: FILTER(B2:B8,A2:A8=E1,"").
  • TEXTJOIN results are limited to 32,767 characters.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Return multiple matching values in one cell

In E2, list every member of the team in E1 in one cell, separated by commas.

Questions

How do I list the matches one per line in the cell?

Use a line break as the separator: =TEXTJOIN(CHAR(10),TRUE,FILTER(B2:B8,A2:A8=E1)) and turn on Wrap Text.

How do I remove repeats from the list?

Wrap the FILTER in UNIQUE: TEXTJOIN(", ",TRUE,UNIQUE(FILTER(…))).

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.