Excel wildcards: * and ? in formulas
In Excel, * matches any number of characters and ? matches exactly one, but only in functions that take criteria or do lookups: COUNTIF, SUMIF, VLOOKUP, MATCH, XLOOKUP and a few others. =COUNTIF(A:A,"*north*") counts every cell that contains "north". In a plain = comparison or in FILTER, * is just an asterisk.
This page is about using * as a pattern. To type a literal asterisk, match one with ~*, or strip asterisks out of your data, see how to type and find an asterisk in Excel.
Pattern cheat sheet
| Criteria | Matches text that |
|---|---|
"north*" |
Starts with north |
"*north" |
Ends with north |
"*north*" |
Contains north anywhere |
"???" |
Is exactly three characters long |
"A??-*" |
Starts with A, two characters, a hyphen, then anything |
"*" |
Is any text at all (numbers and blank cells don't count) |
"<>*north*" |
Doesn't contain north (blank cells and numbers count here too) |
Wildcards ignore case, so "*north*" also matches North and NORTH.
Which functions accept wildcards
| Accepts * and ? | Treats * as a plain character |
|---|---|
| COUNTIF, COUNTIFS | = comparisons, such as =A2="north*" |
| SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS | IF on its own |
| MAXIFS, MINIFS | FILTER, UNIQUE, SORT |
| VLOOKUP and HLOOKUP, with FALSE as the last argument | FIND, EXACT |
| MATCH, with 0 as the match type | SUBSTITUTE, REPLACE |
| XLOOKUP and XMATCH, with match mode 2 | SUMPRODUCT |
| SEARCH | |
| DSUM, DCOUNT and the other database functions |
XLOOKUP is the one people miss. By default it matches exactly, so wildcards do nothing until you set the fifth argument to 2:
=XLOOKUP("*north*", A2:A100, B2:B100, "Not found", 2)
Wildcards in FILTER and IF
FILTER and IF don't read wildcards, but they accept any test that returns TRUE or FALSE. For "contains", use SEARCH, which returns a position when it finds the text and an error when it doesn't:
=FILTER(A2:B100, ISNUMBER(SEARCH("north", A2:A100)))
=IF(ISNUMBER(SEARCH("north", A2)), "Yes", "No")
COUNTIF on a single cell also works as a wildcard test, because it returns 1 or 0:
=IF(COUNTIF(A2, "north*"), "Starts with north", "")
In Microsoft 365, REGEXTEST handles patterns that wildcards can't express. The third argument, 1, makes it ignore case:
=FILTER(A2:B100, REGEXTEST(A2:A100, "^north\s+\d+$", 1))
Wildcards don't match numbers
Wildcards only match text. If A2:A100 holds the numbers 120, 135 and 210, =COUNTIF(A2:A100, "1*") returns 0, not 2. Convert the numbers to text inside the test instead:
=SUMPRODUCT(--(LEFT(A2:A100, 1) = "1"))
The same goes for product codes Excel has stored as numbers. If a lookup with a wildcard fails on codes that look right, check whether they're aligned to the right of the cell, which is Excel's sign that a value is a number.
Google Sheets
COUNTIF, SUMIF, VLOOKUP, MATCH and XLOOKUP accept * and ? in Google Sheets too, and ~* matches a literal asterisk there as well. Two differences are worth knowing:
- REGEXMATCH is available in every version of Sheets, so
=FILTER(A2:B100, REGEXMATCH(A2:A100, "(?i)north"))is the usual "contains" filter. - QUERY uses SQL-style patterns, so inside a query you write
where A like '%north%'with%and_, not * and ?. Our SQL guide explains why.