Logical
Check if a cell contains all required keywords (and no excluded words)
Screen resumes, logs, or SKUs for ALL required terms, ANY optional term, and ZERO excluded terms using AND, OR, ISNUMBER, ISERROR, and SEARCH.
Combine inclusion (ISNUMBER) and exclusion (ISERROR) rules
ISNUMBER(SEARCH(Keyword, Cell)) returns TRUE when a substring appears anywhere in Cell (case-insensitively) and FALSE when absent; ISERROR(SEARCH(Excluded, Cell)) returns TRUE strictly when the banned word is absent.
Nesting these inside AND and OR expresses rich screening filters—for example: must mention BOTH "SQL" and "Python", AND must NOT mention "Intern".
| ProfileText (A) | Has SQL & Python? | Excludes Intern? | Qualified? |
|---|---|---|---|
| Senior SQL and Python Engineer | TRUE | TRUE | TRUE |
| Summer SQL & Python Intern | TRUE | FALSE | FALSE |
| Senior SQL Analyst | FALSE | TRUE | FALSE |
Contains ALL required terms and NO excluded term
Replace SEARCH with FIND only when keyword matching must be strictly case-sensitive.
=AND(ISNUMBER(SEARCH(B2, A2)), ISNUMBER(SEARCH(C2, A2)), ISERROR(SEARCH(D2, A2)))Contains AT LEAST ONE keyword (OR rule)
Use OR when either skill qualifies the row.
=OR(ISNUMBER(SEARCH(B2, A2)), ISNUMBER(SEARCH(C2, A2)))Inputs and interpretation
SEARCH matches substrings, not whole words: SQL can match NoSQL and Intern can match International. It is case-insensitive and supports wildcard patterns; escape * and ? for literal matching. Required and excluded keywords must be nonempty because searching for an empty string succeeds. Errors in the source need separate handling; a keyword match alone does not establish a person’s qualifications.
Try the example
Use the original values or change them to test your understanding.