Dynamic Arrays
Filter & Flag Rows Containing Any Keyword from a Watchlist (SEARCH + FILTER)
Screen support tickets or bank-memo descriptions against multiple case-insensitive watchlist substrings using OR(ISNUMBER(SEARCH(...))) and FILTER.
Case-insensitive multi-keyword screening when exact equality misses embedded substrings!
Exact COUNTIF($E$2:$E$4, B2) only matches when the ENTIRE cell equals a watchlist word—failing completely on free-text descriptions like "Urgent: Wire chargeback dispute" or "API timeout on checkout"! Conversely, case-insensitive SEARCH($E$2, B2) finds substrings anywhere inside B2 regardless of upper/lower case and returns a character position (number) on hit or #VALUE! on miss!
Wrapping ISNUMBER(SEARCH(...)) inside OR(...) into a clean row indicator C2 = --OR(ISNUMBER(SEARCH($E$2, B2)), ISNUMBER(SEARCH($E$3, B2)), ISNUMBER(SEARCH($E$4, B2))) flags every row matching AT LEAST ONE watchlist term—feeding FILTER(A2:A6, C2:C6 = 1, "No Watchlist Hits") cleanly!
| Support Ticket Description (Watchlist: "chargeback", "fraud", "outage") | Row Hit Flag C2:C6 | Extracted Ticket IDs |
|---|---|---|
| TKT-101 "Card Chargeback Claim" & TKT-104 "Checkout Outage" | 1 (Case-Insensitive Match) | "TKT-101, TKT-104" |
| TKT-102 "Password Reset", TKT-103 "Invoice PDF", TKT-105 "Seat Upgrade" | 0 (Routine Tickets) | Excluded from escalation queue |
| Watchlist Changed to Non-Matching Terms | All 0 | "No Watchlist Hits" |
Extract All Ticket IDs Matching Any Watchlist Substring (TEXTJOIN + FILTER)
Filters Ticket_IDs A2:A6 where multi-keyword flag C2:C6 = 1, returning "No Watchlist Hits" when none match.
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, C2:C6 = 1, "No Watchlist Hits"))Count Total Escalated Rows Matching One or More Watchlist Terms
Counts flagged rows without double-counting a ticket that mentions two watchlist words in the same memo.
=SUM(C2:C6)Try the example
Use the original values or change them to test your understanding.