TAKEAWAYS
In card games, wildcards are cards that can represent any other card. In computing, they serve a similar purpose, helping us move beyond exact matches. Like in card games, a wildcard character can represent one or more other characters, making lookups, filters, and searches far more flexible.
Accountants are typically very organised. Account codes and names often follow defined numbering or naming conventions. Likewise, transaction descriptions and report line captions usually follow consistent patterns.
By adopting consistent patterns or conventions, we can write cleaner formulas, maintain a clearer working paper structure, and build templates that are easier to scale.
Excel supports two wildcards. They are powerful when used right:
Note: Wildcards only work with text. If your account codes or invoice numbers are stored as numbers, convert them to text first. A good rule of thumb I always follow: if you do not perform arithmetic calculations on the values, store them as text.
Our favourite lookup functions (XLOOKUP, VLOOKUP, HLOOKUP, and MATCH) as well as conditional functions like COUNTIF, COUNTIFS, SUMIF, and SUMIFS, all support wildcards. However, some functions require specific arguments (such as using exact match mode) for wildcard support to work properly. Even Ctrl + F (Find) supports wildcards, allowing for flexible searching within the workbook.
1. “*” Match any number of characters
In Figure 1, you have a trial balance with columns for Account Name, Account Code, and Amount. Suppose you want to calculate the total for Trade Receivables – both current and non-current. These accounts may appear under different names and codes, such as:
25000 Trade receivables – current
25001 Trade receivables – non-current
25002 Trade receivables – related party (current)
Figure 1
We should avoid writing formulas that add up individual cells manually. Even if we use lookup or SUMIF functions, without wildcards, the formula quickly becomes long and unscalable. For example:
= SUM(
SUMIF(AccountNameRange, "Trade receivables – current", AmountRange),
SUMIF(AccountNameRange, "Trade receivables – non-current", AmountRange),
SUMIF(AccountNameRange, "Trade receivables – related party", AmountRange)
)
Every time a new Trade Receivable account is added, you need to update the formula again. That is neither efficient nor scalable.
All of these accounts share a common prefix in their names: “Trade receivables”. Instead of summing them one by one or a separate SUMIF for each criterion, you can use the * wildcard to match all rows where the account name starts with that phrase.
Formula: =SUMIF(AccountNameRange, "Trade receivables*", AmountRange)
This includes any account where the name begins with “Trade receivables”, no matter what follows. The * matches everything after that (spaces, hyphens or extra text) making your formula flexible and future-proof.
It is a simple and effective way to group similar accounts, as long as naming is consistent.
Figure 2
2. “?” Match exactly one character
Use this when the account codes follow a structured format. If your account codes follow a fixed pattern, the “?” wildcard can help match specific character positions.
In the above example, all Trade Receivables accounts have five-digit codes starting with 250. Here, the ? wildcard matches exactly one character. Since all codes are five digits long and you want to capture any account that starts with 250 followed by two more characters, use:
Formula: =SUMIF(AccountCodeRange, "250??", AmountRange)
Figure 3
This pattern ensures that only account codes with exactly two characters after 250 are included – such as 25000 to 25099. It is clean, scalable, and ideal for structured account coding.
Wildcards are supported in several Excel formulas. Here is how and where to use them effectively:
✔ SUMIF and SUMIFS
These work with wildcards by default – no special setting needed (refer to examples above).
✔ VLOOKUP
Also supports wildcards by default, but make sure the final argument (range_lookup) is set to FALSE for exact match, as required for non-numerical lookups.
✔ XLOOKUP
Supports wildcards only when you turn on match mode 2
If you ever need to search for the actual characters “*” or “?”, Excel requires a ~ before them (example, ~* to match a real asterisk). However, relying on this can be messy and confusing for typical Excel users. A better approach is to avoid using wildcard characters in your naming conventions altogether. This keeps your lookup keys clean, consistent, and less confusing, especially when building formulas that need to scale.
Wildcards are simple yet powerful tools in Excel. They allow accountants to work with patterns instead of exact values.
Whether you are summing Trade Receivables, filtering current assets, or performing a quick lookup based on part of an account name, wildcards like “*” and “?” make your formulas more flexible and scalable.
For accountants working with real-world data, they are a practical shortcut well worth mastering.
Ng Xian Hui, CA (Singapore), is Founder of Backbone Pte Ltd.
© 2024 Institute of Singapore Chartered Accountants
