Logical & Conditional
Test Whether a Range Contains One of Many Exact Values (Set Overlap Audit)
Determine whether an entire column or manifest range contains at least one exact code from a restricted or priority list using whole-cell equality or COUNTIF checks without partial-prefix false positives.
Whole-cell membership across two ranges vs substring wildcards
Compliance and hazmat shipping checks often ask a batch-level question: does shipment manifest B2:B6 contain any restricted UN code from watchlist D2:D4? Unlike substring searches, exact membership must match whole cell values so code "UN-12" never falsely triggers on "UN-1203".
Summing whole-cell equality across the watchlist via SUMPRODUCT((B2:B6 = D2) + (B2:B6 = D3) + (B2:B6 = D4)) > 0 tests exact set intersection cleanly, ignores partial prefixes, and works equally well on alphanumeric codes and numeric IDs.
| Manifest Item Code (B2:B6) | Hazmat Watchlist (D2:D4) | Exact Matches | Hold Shipment? |
|---|---|---|---|
| UN-1203 | UN-12 | 0 (Prefix only, not exact) | No |
| PKG-440 | UN-1950 | 1 (Matches Row 5) | Yes |
| BOX-810 | UN-3480 | 0 | No |
| UN-1950 | (Batch status) | Total = 1 | Hold Required |
Exact Range-to-List Set Intersection Flag
Returns TRUE when at least one cell in B2:B6 equals an exact non-blank code in D2:D4.
=SUMPRODUCT(((B2:B6 = D2) * (LEN(D2) > 0)) + ((B2:B6 = D3) * (LEN(D3) > 0)) + ((B2:B6 = D4) * (LEN(D4) > 0))) > 0Count of Restricted Line Items in the Manifest
Counts how many manifest rows in B2:B6 match any exact code in the distinct watchlist D2:D4.
=SUMPRODUCT(((B2:B6 = D2) * (LEN(D2) > 0)) + ((B2:B6 = D3) * (LEN(D3) > 0)) + ((B2:B6 = D4) * (LEN(D4) > 0)))Try the example
Use the original values or change them to test your understanding.