Text
Concatenate IF: join matching rows into a comma-separated list
Combine TEXTJOIN and FILTER to roll up every name or SKU matching a category into one clean comma-separated cell.
Roll up one-to-many matches into a single cell
XLOOKUP returns only the first rep in a region. To display all reps assigned to "EMEA" inside one summary cell ("Nora, Tariq, Elena"), filter the name column first and pass that filtered array into TEXTJOIN.
Passing "" as FILTER's third if_empty argument and TRUE as TEXTJOIN's ignore_empty argument ensures unmatched regions return an empty string (or a custom label) without #CALC! errors.
The example assumes every matching row has a populated name. Filter out empty names explicitly when importing incomplete records; a genuinely blank source cell can appear as zero in Excel array results. Keep the combined text within Excel’s 32,767-character limit.
| RepName (A) | Region (B) | Included in EMEA Rollup? |
|---|---|---|
| Nora | EMEA | Yes (1st) |
| Liam | AMER | No |
| Tariq | EMEA | Yes (2nd) |
| Mei | APAC | No |
| Elena | EMEA | Yes (3rd) |
TEXTJOIN + FILTER conditional concatenation
For PickRegion = "EMEA", FILTER extracts ["Nora"; "Tariq"; "Elena"] and TEXTJOIN joins them with ", ".
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6=C2, ""))Custom empty-region fallback
Wrap with LET to display "None assigned" when a region has zero reps.
=LET(s, TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6=C2, "")), IF(s="", "None assigned", s))Try the example
Use the original values or change them to test your understanding.