Wildcard is a term for a special kind of a character that can represent one or more “unknown” characters, and Excel has a wildcard character support. You can use wildcards for filtering, searching, or inside the formulas. In this guide, we’re going to show you how to use Excel Wildcard characters for setting up formula criteria.

Download Workbook

Excel wildcard characters

Excel supports 3 kinds of wildcard characters:

Asterisk (*) Any value of zero or more
Question mark (?) Any single character
Tilde (~)

Escape for an actual question mark, asterisk, or tilde character.

  • A literal asterisk  (~*)
  • A literal question mark (~?)
  • A literal tilde (~~)

You can use these characters to generate a text pattern for strings that are to be matched. Please note that wildcard characters only work with texts, and do not work with numbers.

Let’s look at some examples:

Pattern Meaning Sample
? Any one character “A”, “a”, “1”, “-“, etc.
?? Any two characters “A1”, “9a”, “9.”, etc.
* Any characters “excel”, “supp0rtz”, “wi!d cards”, etc.
A* Starts with “A” “A”, “Anchor”, “A string”, etc.
?* At least one character “Z”, “1”, “Z1”, etc.
(???) ???-???? 10 characters with parenthesis and a hyphen “(123) 456-7890”, “(A10) XYZ-8866”, etc.
*lec* Contains “lec” “electric”, dialectic”, “lecture”, etc.
*~? Ends with a question mark (?) “O Brother, Where Art Thou?”, ” Dude, Where’s My Car?”, etc.

Formulas that support using wildcard criteria

All …IFS and …IF Functions

All statistical functions in Excel that end with either “IFS” or “IF” support wildcards.

Using wildcard criteria can increase the versatility of these functions. Use strings with wildcards in criteria arguments. The following examples show the difference between using and not using wildcards.

How to use Wildcard criteria in Excel formulas - ...IFS

The upper set of formulas are using the “*FIRE*” string which represents any text that contains “FIRE”. Thus, the formulas calculates 3 rows of data: “FIRE”, “FIRE” and “FIRE, FLYING”.

On the other hand, the formulas work on only two of examples without any wildcards: “FIRE” and “FIRE”.

VLOOKUP and HLOOKUP

Both VLOOKUP and HLOOKUP functions support wildcard characters. Although both functions have an approximate match mode, using them in this mode may not return the correct result every time. Wildcards gives you more precision on your search. Use a string with a wildcard for lookup value argument to search.

The following screenshot shows an example for each formula. VLOOKUP function searches the “C*n” value, and matches with “Charmeleon”. On the other side, HLOOKUP function searches a two-character string that matches “HP”.

MATCH

MATCH function is another lookup function that support wildcard characters. Aside from returning a value on a different column, MATCH function returns the position of the found value. Once again, use wildcard characters in the lookup value argument.

SEARCH

You can use the SEARCH function with wildcards to find a string pattern in another string. In our example, we searched for a pattern like “a?to*e” to locate a string starts with “a”, followed by any single character, which is followed by “to”, and any number of characters until an “e” character is found.