Excel offers nearly 20 worksheet functions that support the ability to use wildcards in their arguments. You’ll find them listed below, with links to Microsoft’s help topic about each of them.
And here are the characters you can use to filter characters in those functions:
| USE… | TO FIND…. |
| ? (question mark) | Any single character. For example, “Bl?ck” finds either “Black” or “Block” |
| * (asterisk) | Any number of characters. For example “North*” finds “North”, “Northeast”, and “Northwest”. And “(*)” finds any content enclosed by parentheses. |
| ~ (tilde) followed by ?, *, or ~ | A literal question mark, asterisk, or tilde. For example… …To find “West*” use “West~*” …To find “West?” use “West~?” …To find “West~” use “West~” …To find “West~?” use “West~~?” |
Excel Functions That Use Wildcards |
|
| AVERAGEIF | (Use AVERAGEIFS.) Returns the average (arithmetic mean) of all the cells in a range that meet one criteria. |
| AVERAGEIFS | Returns the average of all cells that meet one or more sets of criteria. |
| COUNTIF | (Use COUNTIFS.) Counts the number of cells that meet one criteria. |
| COUNTIFS | Counts the number of cells that meet one or more sets of criteria. |
| DPRODUCT | (Seldom used.) Multiplies the values in a field (column) of records in a list or database that match conditions that you specify. |
| DSTDEVP | (Seldom used.) Calculates the standard deviation of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify. |
| DSUM | (Seldom used.) Adds the numbers in a field (column) of records in a list or database that match conditions that you specify. |
| DVARP | (Seldom used.) Calculates the variance of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify. |
| HLOOKUP | (INDEX-MATCH preferred.) Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. |
| MATCH | Searches for a specified item in a range of cells, and then returns the relative position of the item in the range. For example, if the list of month abbreviations are in the range A3:A14, and MATCH searches for “Feb”, MATCH returns 2, because it’s the second item in the range. |
| MAXIFS | Returns the maximum value among cells specified by one or more sets of criteria. |
| MINIFS | Returns the minimum value among cells specified by one or more sets of criteria. |
| SEARCH | Finds one text value within another (not case-sensitive). |
| SEARCHB | Works like the SEARCH function, but counts 2 bytes per character when a DBCS language is set as the default language. |
| SUMIF | (Use SUMIFS.) Adds the cells in one or more rows or columns specified by one criteria. |
| SUMIFS | Adds the cells in one or more rows or columns specified by multiple criteria. |
| VLOOKUP | (INDEX-MATCH preferred.) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. |
