Wildcard Characters

Wildcard characters are special characters that can replace other characters in a search:

Usage Example

In this example, the objective is to sum all the apples:

google sheets search wildcard characters

Use the SUMIF function and complete it:

google sheets search sum if wildcard characters

You can see that it does not work here because the function searches for cells that exactly match "Apple," and there are none.

To search for all cells containing "Apple," add the character "*" before and after, "*Apple*":

google sheets sumif wildcard character characters

This time, the function correctly performed the sum of the 3 lines of apples.

To avoid having to enter these characters for each search, you can add them directly to the formula:

google sheets sumif wildcard character asterisk characters

Advanced Usage Example

In this second example, the objective is to use the 3 wildcard characters to fully understand their utility.

We start with the XLOOKUP function and a search without wildcard characters:

google sheets xlookup wildcard characters
To be able to use wildcard characters with this function, you must enter "2" in "search_mode".

The goal now is to target a fruit that ends with a letter followed by a "?" (thus targeting "Apple A?").

So let's start with a "*", then a space, then a "?" to replace a letter, then the character "?" and see what happens:

google sheets xlookup wildcards wildcard characters

The search "* ??" matches any fruit that ends with a space followed by 2 characters (in this case, "Apple G+" is the first cell to match this search).

To get a fruit that ends with a "?", we must ensure that this character is not considered a wildcard character, and to do this, it must be preceded by a "~":

google sheets xlookup wilcards wildcard characters

This time, it is indeed the quantity of "Apple A?" that is returned.


The use of wildcard characters is only available with certain Google Sheets functions such as:

If needed, you can copy the Google Sheets document (or view the document) with these examples.