Logical & Conditional
IF with Wildcards: Pattern Matching via COUNTIF & ISNUMBER(SEARCH)
Perform wildcard pattern tests (* and ?) inside IF statements—where direct =IF(A2="*PRO*", ...) fails—by pairing IF with COUNTIF(A2, pattern) > 0 or ISNUMBER(SEARCH()).
Why =IF(A2 = "*PRO*", ...) ALWAYS returns FALSE—unless A2 literally contains asterisks!
One of the most common spreadsheet surprises is discovering that the equality operator (=) inside IF treats asterisks (*) and question marks (?) as literal punctuation characters rather than wildcards! Writing =IF(A2 = "*PRO*", "Tier 1", "Standard") only matches a cell whose text literally contains "*PRO*"!
To unlock full Excel wildcard pattern matching—including multi-character *, single-character ?, and positional masks like "INV-????-EU"—wrap a single-cell COUNTIF(A2, E2) > 0 inside IF! Need a simple substring check without wildcard syntax? Pair IF(ISNUMBER(SEARCH("pro", A2)), ...) for case-insensitive substring detection or FIND() for case-sensitive matching!
| SKU / Invoice Code (A) | Wildcard Pattern (E2 = "INV-????-EU") | COUNTIF(A, E2) > 0 | IF Output Label |
|---|---|---|---|
| "INV-4082-EU" | 4 chars between INV- and -EU | TRUE (1) | "EU_Valid_Format" |
| "INV-99-EU" | Only 2 middle chars (fails ????) | FALSE (0) | "Non_Matching_Code" |
| "INV-7710-US" | Ends in -US instead of -EU | FALSE (0) | "Non_Matching_Code" |
IF Statement with Full * and ? Wildcard Mask (COUNTIF Bridge)
Evaluates single-cell COUNTIF(A2, $E$2) > 0 so IF supports any combination of * and ? wildcards.
=IF(COUNTIF(A2, $E$2) > 0, "EU_Valid_Format", "Non_Matching_Code")Count Rows Matching Wildcard Pattern Across Table
Applies the exact same wildcard mask $E$2 across A2:A6 to audit batch pattern compliance.
=COUNTIF(A2:A6, $E$2)Try the example
Use the original values or change them to test your understanding.