Return ALL Matches in One Cell: TEXTJOIN + FILTER (the VLOOKUP Upgrade)

📎 This article includes 2 downloadable practice files ↓

A classic request: “Which products did the North region buy?” VLOOKUP and XLOOKUP stop at the first match, so you get “Keyboard” and miss the other five. One formula combination fixes it and updates itself when the data changes.

Try it live in the Formula Lab → “FILTER · SORT · UNIQUE” tab → “All products per region in one cell”.

The formula

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B100, A2:A100 = H2, "")))

Read it from the inside out:

1. FILTER(B2:B100, A2:A100 = H2) — every product where the region equals H2.
2. UNIQUE(…) — removes repeats, so “Mouse” appears once even if it was sold ten times.
3. TEXTJOIN(“, “, TRUE, …) — glues the list into one cell with a comma between items; TRUE skips blanks.

The final "" in FILTER is what to return when nothing matches — without it you get #CALC!.

Variations you will actually use

Older Excel (2016 and earlier)

FILTER, UNIQUE and dynamic arrays need Excel 2021 or Microsoft 365. TEXTJOIN exists from Excel 2019. For older versions a tiny custom function does the job:

Function JoinMatches(lookup As Variant, keys As Range, vals As Range, Optional sep As String = ", ") As String
    Dim i As Long, out As String
    For i = 1 To keys.Rows.Count
        If keys.Cells(i, 1).Value = lookup Then
            If InStr(sep & out & sep, sep & vals.Cells(i, 1).Value & sep) = 0 Then
                out = out & IIf(out = "", "", sep) & vals.Cells(i, 1).Value
            End If
        End If
    Next i
    JoinMatches = out
End Function
' =JoinMatches(H2, A2:A100, B2:B100)

The InStr check skips duplicates, so it behaves like UNIQUE.

Where this saves real time

• A customer summary sheet: all invoice numbers per customer in one cell.
• Project trackers: every owner assigned to a project.
• SAP/Oracle exports: all cost centres that posted to an account.

Try it yourself: step by step

1. Download all-matches-practice.xlsx and open the All matches sheet.
2. Column B lists every product each region bought, alphabetically and without repeats, e.g. Keyboard, Monitor, Mouse, Webcam.
3. Click B2 and read the formula from the inside out: FILTER → UNIQUE → SORT → TEXTJOIN.
4. On the Sales sheet change one row’s Region from North to South. Both lists update instantly.
5. Change the separator from ", " to CHAR(10), then turn on Wrap Text for column B to get one product per line.
6. Add a second condition: only products with quantity 10 or more. Replace the FILTER part with FILTER(Sales!E2:E61,(Sales!C2:C61=A2)*(Sales!F2:F61>=10),"").

Frequently asked questions

Why do I get #CALC!?

FILTER found nothing and you did not supply the third argument. Add "" (or “None”) as the last FILTER argument.

Is there a limit to how much TEXTJOIN can return?

A cell holds up to 32,767 characters; beyond that TEXTJOIN returns #VALUE!. For huge lists use a pivot table or FILTER into a column instead.