Wildcards are often overlooked as one of the more useful features of Excel. Using these characters can increase efficiency and flexibility when searching through text data. Wildcard characters are used in a variety of Excel functions ranging from VLOOKUPS to SUMIFS. We will take a closer look at why some functions use them and others don't later in this post.
There are two types of wildcards in Excel which act as a placeholder when searching strings of text for partial data matches, as well as an "anti-wildcard." They are:
| Wildcard | Meaning |
|---|---|
| * | Wildcard of undefined length |
| ? | Single character wildcard |
| ~ | Not a wildcard, but used to identify an actual question mark or asterisk in text. |
The ? (question mark) replaces any single character. For example, "Lemo?" would match with the text "Lemon" because the question mark replaces the single "n." "Lem?" would not match with "Lemon" because the question mark replaces only one character, and "Lemon" has five characters in total.
The * (asterisk) replaces any string of characters of any length. In continuing with our "Lemon" example, the following would all match because the asterisk can replace any number of characters (including zero).
"Lemon*""L*""*""L*n""*L*""*on"
The ~ (tilde) is used to indicate a literal asterisk or question mark that is part of the search string and not a wildcard. It signals that the asterisk or question mark is actually part of the text you are searching for.
For example, if I was searching for a cell which contained the text "Hello?" with an actual question mark, I could use a tilde to indicate to Excel that the question mark is not a wildcard character.
"Hello~?"
Use this example spreadsheet to follow along with the example formulas using wildcard characters.
Say you need to find the ID of an employee you just met, but you don't know his last name. You do know that his first name is John. You can use the VLOOKUP function combined with the asterisk wildcard to replace the unknown last name, effectively searching for any text which starts with "John" regardless of how it ends. Double-click into a cell in the example spreadsheet above and type this formula.
=VLOOKUP("John*", A:C, 2, false)Now say you know John's first name and last name, but you can't remember what his middle initial is. You can use the question mark wildcard to replace exactly one character (the middle initial).
=VLOOKUP("John ?. Reilly", A:C, 2, false)Note that the asterisk wildcard would also work in this situation, but the question mark ensures that you replace only one character to avoid any unintentional matches.
Say you have an employee whose last name is Adams, but you can't remember her first name. You want to know how many deals she has closed. You can use the XLOOKUP function with the asterisk wildcard to look this up.
In order to use wildcard matching with the XLOOKUP function, you need to enter 2 as the match type, which is the 4th argument. Type this formula into the example spreadsheet above:
=XLOOKUP("*Adams", A:A, C:C, "Employee not found", 2)Now say you know there is an employee with exactly 8 characters in her name (remember, the space is a character). You can use the question mark wildcard to look up the number of deals closed for the employee with 8 characters in their name.
=XLOOKUP("????????", A:A, C:C, "Employee not found", 2)You can use wildcard characters for matching with the COUNTIF function in Excel. Say you want the count of employees with the last name "Ferrell." Type the following formula into the example spreadsheet above:
=COUNTIF(A:A, "*Ferrell")This formula will return a value of 1 because there is one cell in the range A:A which contains text that ends with "Ferrell."
Now say you want to count the number of employees with "ll" (double L's) in their names.
=COUNTIF(A:A, "*ll*")This formula will return a value of 2 because there are two employees whose names contain "ll" somewhere in them.
The IF function in Excel does not natively support wildcard matching, but we can work around this by combining it with the COUNTIF function (see COUNTIF example above).
Say you wanted to write a formula that returns "Yes" if the name in the cell starts with "Amy" and "No" if it does not. You can write the following IF statement which utilizes COUNTIF:
=IF(COUNTIF(A5, "Amy*"), "Yes", "No")If the text in cell A5 starts with "Amy" then the COUNTIF function will return a value of 1 because it counted 1 cell which matched the criteria. Excel treats 1 the same as true, so the IF statement will return "Yes." If the text in the cell does not begin with "Amy" the COUNTIF function will return 0, which Excel treats as false.
Now say your boss, for whatever reason, wants to find the total number of deals closed by employees who have an "a" in their name. You can use the SUMIF function with wildcard matching to accomplish this task.
Type the following formula into the example spreadsheet:
=SUMIF(A:A, "*a*", C:C)Because three employees have an "a" somewhere in their name, the SUMIF function returns the total number of deals closed (column C) by the employees in column A who match the criteria.
Now say your boss wants to find the total sum of deals closed by all employees whose names start with the letter W. You can use SUMIF to find this information as well.
=SUMIF(A:A, "W*", C:C)Because only one employee's name begins with the letter "W" followed by any number of characters, this formula returns the sum for only one employee, which is 4.
To sum it all up, you can combine wildcard matching with a variety of functions in Excel to search for approximate matches of text. The question mark wildcard replaces one character in text, while the asterisk replaces any number of characters. The best way to get the hang of these characters is to get your hands dirty and try a few examples on your own.