Excel has three wildcard characters for criteria and lookups: * matches any number of characters (including none), ? matches exactly one character, and ~ turns the next * or ? back into a plain character. =COUNTIF(A2:A7,"*apple*") counts the cells that contain apple anywhere.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Pattern | Count | |
| 2 | Apple juice | *apple* | 4 | |
| 3 | Green apple | apple* | 2 | |
| 4 | Pineapple | *juice | 2 | |
| 5 | Orange juice | ????? | 0 | |
| 6 | Pear | *e | 4 | |
| 7 | Apples |
*apple*containsapple: 4 matches, becausePineapplecounts too.apple*begins withapple: onlyApple juiceandApples. COUNTIF ignores case.*juiceends withjuice.?????is exactly five characters long. None of these products has five characters, so 0; typePeachin A6 and it counts 1.*eends withe.
Type your own pattern in column C, such as *an* or P*, and the count updates.
Partial match in VLOOKUP and XLOOKUP
VLOOKUP accepts wildcards in exact-match mode (FALSE as the last argument). Join the wildcard to the value in the formula, so only the first letters go in D2:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Starts with | Price | |
| 2 | Apple juice | 3.5 | Pin | 4 | |
| 3 | Green apple | 1.2 | 4 | ||
| 4 | Pineapple | 4 | |||
| 5 | Orange juice | 3.2 | |||
| 6 | Pear | 0.9 |
Both formulas find Pineapple. Change D2 to juice: VLOOKUP's pattern juice* needs the text to start with juice and returns #N/A, while the XLOOKUP pattern *juice* finds the first product that contains it, Apple juice. Like every exact match, a wildcard lookup returns the first row that fits, so make the pattern specific enough.
XLOOKUP only treats * and ? as wildcards when its fifth argument, match_mode, is 2. Without it, it looks for the asterisk literally. MATCH accepts wildcards with match_type 0, and XMATCH with match_mode 2, just like XLOOKUP. See VLOOKUP for the rest of its arguments.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Phone products | ||
| 2 | Smartphone | |||
| 3 | Headphones | |||
| 4 | Phone stand | |||
| 5 | Laptop bag | |||
| 6 | Charger |
Your turn: In D2, count the products whose name contains phone anywhere.
Sum and average with a wildcard
Every function that takes criteria reads them the same way, so the same patterns work in SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Pattern | Total | |
| 2 | North-East | 120 | North* | 285 | |
| 3 | North-West | 95 | *West | 155 | |
| 4 | South | 80 | ????? | 150 | |
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
North* adds every region that starts with North, including North itself, because * also matches nothing. ????? adds the regions with exactly five characters: South and North.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | West total | ||
| 2 | North-East | 120 | |||
| 3 | North-West | 95 | |||
| 4 | South | 80 | |||
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
Your turn: In E2, total the sales of every region whose name ends with West.
Find a real asterisk or question mark with ~
To count text that contains an actual * or ?, put a tilde in front of it. The tilde itself is written ~~.
| A | B | C | |
|---|---|---|---|
| 1 | Note | Count | |
| 2 | Rated 5* | 1 | |
| 3 | Why? | 1 | |
| 4 | Done | 4 | |
| 5 | 5 stars |
C2 counts the cells containing a literal asterisk (A2 only), C3 those containing a question mark. C4 shows the other side: "*" alone matches any text, so it counts every text cell, 4 here. It skips numbers and empty cells, which makes COUNTIF(range,"*") the usual way to count text cells.
Which functions accept wildcards
Accept *, ?, ~ | Do not |
|---|---|
| COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS | =, <> and other comparisons |
| VLOOKUP and HLOOKUP with FALSE | IF on its own |
| MATCH with 0 | FIND |
| XLOOKUP and XMATCH with match_mode 2 | FILTER, UNIQUE, SORT |
| SEARCH | SUBSTITUTE, TEXTBEFORE, TEXTAFTER |
| Find and Replace (Ctrl+H), filter boxes |
SEARCH takes wildcards inside a formula: =SEARCH("b?d","a bad day") returns 3 in Excel (FIND and SEARCH has the details). For FILTER, use ISNUMBER(SEARCH(...)) as the condition instead of a pattern.
Common mistake: a wildcard after =
The = operator never reads wildcards: =A2="*apple*" asks whether A2 holds the seven characters *apple*. In Excel, with Green apple in A2:
=A2="*apple*" FALSE
=IF(A2="*apple*","yes","no") no
Put the test in COUNTIF instead, which returns 1 or 0 for a single cell, or use SEARCH:
| A | B | C | |
|---|---|---|---|
| 1 | Product | COUNTIF test | SEARCH test |
| 2 | Green apple | contains apple | contains apple |
| 3 | Pear | no | no |
IF treats the 1 from COUNTIF as TRUE and the 0 as FALSE. The SEARCH version needs no wildcards at all, because SEARCH already looks for the text anywhere in the cell.
Frequently Asked Questions
What are the wildcard characters in Excel?
* matches any number of characters, including none; ? matches exactly one character; ~ before *, ? or ~ makes it a plain character. "*apple*" means contains apple, "A*" begins with A, "???" exactly three characters.
How do I use a wildcard in VLOOKUP?
Join the wildcard to the lookup value and use exact match: =VLOOKUP(E2&"*",A2:B6,2,FALSE) finds the first entry that starts with E2. In XLOOKUP, set match_mode to 2: =XLOOKUP("*"&E2&"*",A2:A6,B2:B6,"none",2).
Why doesn't a wildcard work in my IF formula?
The = comparison does not understand wildcards, so =IF(A2="*apple*",...) only matches the literal text *apple*. Use =IF(COUNTIF(A2,"*apple*"),"Yes","No") or =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No").
How do I count cells that contain an asterisk?
Put a tilde before it: =COUNTIF(A2:A10,"*~**"). The first and last * are wildcards, and ~* is a literal asterisk.
