Lookup & Reference
Check submitted codes against a master list with literal comparisons
Flag missing codes and list unmatched entries without treating wildcard characters as search patterns.
Check membership without masking data errors
Each helper cell compares one submitted code with the four master entries using SUMPRODUCT(--(master=code)). This treats characters such as * and ? as literal code characters instead of COUNTIF wildcard patterns. Keep the helper formulas intact.
This fixed example requires every submitted and master code to be present. Blank cells return Invalid inputs rather than being ignored. Error cells should be corrected; the final formula does not turn arbitrary calculation errors into All Valid.
List membership is a data-quality check, not proof of authorization or permission to post a transaction. Duplicate submitted codes remain repeated in the output; duplicate master entries still count as membership. Normalize whitespace deliberately before comparison if that is the required policy.
| Submitted Code (A) | Approved Master List (D2:D5) | Match Count (B) | Audit Status |
|---|---|---|---|
| CC-100 | CC-100 | 1 | Approved |
| CC-200 | CC-200 | 1 | Approved |
| CC-999 | CC-300 | 0 (Missing!) | Unauthorized code! |
| CC-300 | CC-400 | 1 | Approved |
| CC-888 | — | 0 (Missing!) | 2 rogue codes: "CC-999, CC-888" |
Boolean check: does the range contain ANY unapproved value?
Returns TRUE when one or more rows in B2:B6 have 0 master-list matches.
=IF(OR(COUNTBLANK(A2:A6)>0,COUNTBLANK(D2:D5)>0),"Invalid inputs",COUNTIF(B2:B6, 0) > 0)Extract comma-separated list of unauthorized values
All Valid is returned only when no helper reports a missing code. Other calculation errors are not caught and disguised as success.
=IF(OR(COUNTBLANK(A2:A6)>0,COUNTBLANK(D2:D5)>0),"Invalid inputs",IF(COUNTIF(B2:B6,0)=0,"All Valid",TEXTJOIN(", ",TRUE,FILTER(A2:A6,B2:B6=0))))Try the example
Use the original values or change them to test your understanding.