Lookup & Reference
Look Up & Concatenate All Matches Into One Comma-Separated Cell (TEXTJOIN + FILTER)
Return every invoice, serial number, or owner matching a lookup key inside a single comma-delimited summary cell using TEXTJOIN(", ", TRUE, FILTER(..., "No Matches")).
Collapsing one-to-many lookup hits into a single cell without spilling over neighbor rows!
XLOOKUP and VLOOKUP stop at the first match, while bare FILTER spills vertically and collides (#SPILL!) whenever you build a compact client summary table with one row per client! Wrapping FILTER(B2:B5, A2:A5 = E2, "No Matches") inside TEXTJOIN(", ", TRUE, ...) gathers every matching invoice ID for client E2 into a clean comma-separated list inside one cell!
Look up client "Alpha" across ledger A2:B5: FILTER extracts ["INV-01", "INV-05", "INV-09"] and TEXTJOIN joins them into "INV-01, INV-05, INV-09" (3 invoices)—while an unmatched client like "Delta" returns FILTER’s third argument "No Matches" cleanly without #CALC! errors!
| Lookup Client (E2) | Matching Count | Single-Cell TEXTJOIN + FILTER Output | Spill Behavior |
|---|---|---|---|
| "Alpha" | 3 matches | "INV-01, INV-05, INV-09" | Stays in 1 cell (safe to copy down) |
| "Beta" | 1 match | "INV-02" | No trailing comma on single item |
| "Delta" (Unmatched) | 0 matches | "No Matches" | Prevents #CALC! empty-filter error |
All Matching Values Joined in a Single Comma-Separated Cell
Filters invoice IDs B2:B5 where client A2:A5 equals E2 (defaulting to "No Matches") and joins non-empty results with ", ".
=TEXTJOIN(", ", TRUE, FILTER(B2:B5, A2:A5 = E2, "No Matches"))Total Match Count for Target Key
Counts how many records in A2:A5 belong to lookup key E2.
=COUNTIF(A2:A5, E2)Try the example
Use the original values or change them to test your understanding.