Dynamic Arrays
Extract common values from two lists (set intersection with FILTER)
Find items that appear in BOTH List A and List B—count shared records and extract a deduplicated overlap list dynamically.
List intersection vs list difference
Whereas reconciliation exceptions ask "what is in List A but missing from List B?", cross-sell and audit overlaps ask the exact opposite: "which IDs exist in BOTH lists?"
Flagging each row of List A with COUNTIF($C$2:$C$4, A2) > 0 creates a transparent boolean mask B2:B5 that feeds UNIQUE(FILTER(A2:A5, B2:B5, "None")) and COUNTIF(B2:B5, TRUE).
| CRM Accounts (A) | In Billing? (B) | Billing Accounts (C) |
|---|---|---|
| ACC-101 | FALSE | ACC-102 |
| ACC-102 | TRUE (Shared) | ACC-104 |
| ACC-103 | FALSE | ACC-999 |
| ACC-104 | TRUE (Shared) | — |
Extract shared items and count intersection size
Wrap FILTER in UNIQUE so repeated keys in List A appear only once in the intersection summary.
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A5, B2:B5, "None")))Count overlapping items
Counts how many rows in List A matched at least once in List B.
=COUNTIF(B2:B5, TRUE)Inputs and interpretation
Helper formulas exclude empty IDs and escape COUNTIF wildcards for literal matching. Matching is case-insensitive. The displayed TEXTJOIN result is one text cell, not a spilled list. COUNTIF counts matching rows including duplicates, whereas UNIQUE deduplicates the displayed list; these are different counts.
Try the example
Use the original values or change them to test your understanding.