Excel: Use Wildcards (*, ?) Instead

Key insights

• Wildcard: The asterisk (*) matches any number of characters and the question mark (?) matches exactly one character.
  Use these to perform flexible text searches without writing long nested formulas.
• * in common functions: Use * with functions like COUNTIFS, SUMIFS, Find & Replace, and the Filter tool to match partial text.
  Example: =COUNTIF(A1:A10,"*apple*") counts cells that contain "apple" anywhere.
• ? for precise patterns: Use ? when you need to match a single unknown character, for example "?at" matches "cat" or "bat".
  This helps when you expect a fixed-length pattern within text.
• XLOOKUP and INDEX‑MATCH with wildcards: Both accept * and ? for approximate or partial matches.
  Note the limitation: these lookups return the first matching result only, not multiple matches.
• FILTER for multiple results: Use the FILTER function to return all rows that match a wildcard pattern when you need multiple matches.
  FILTER works well with dynamic arrays to return lists of matching records instead of a single value.
• Benefits: Wildcards simplify formulas, improve readability, and often speed up troubleshooting and maintenance.
  Combine wildcards with modern Excel functions to replace long, complex formulas with concise pattern-based expressions.

Episode overview: Kenji Farré’s simple shortcut for smarter Excel

Kenji Farré (Kenji Explains) [MVP] released a clear, practical tutorial that urges Excel users to rethink long, nested formulas and instead use two familiar characters: the * and the ?. In his video, Farré demonstrates how these wildcards work across common functions, showing that pattern matching can replace much of the formula complexity many users accept as unavoidable. Consequently, the tutorial aims to help both everyday spreadsheet users and power analysts write shorter, more readable formulas without losing flexibility.

How the * and ? wildcards work in core functions

Farré starts with the basics by explaining that the * matches any number of characters while the ? matches exactly one character. He then applies these behaviors to functions like COUNTIFS and SUMIFS to count or sum items based on partial matches rather than exact strings, thereby simplifying many common lookups and aggregations. The video proceeds step by step, which helps viewers see how a single wildcard pattern can replace several nested conditions and long logical tests.

Practical demos: Find & Replace, Filter, and common lookup patterns

Next, the presenter shows how the wildcards speed up routine tasks such as the Find & Replace tool and the Filter utility, where users often want to locate items that include a fragment of text. He also demonstrates the wildcard with XLOOKUP and INDEX‑MATCH, explaining that these functions accept pattern matches and can resolve many lookup needs without extra string functions. However, Farré points out that XLOOKUP and INDEX‑MATCH typically return only the first match they encounter, which leads to the next part of his tutorial.

Handling multiple matches and limits of single-result lookups

Farré addresses the common challenge of multiple matches by presenting the FILTER function as an alternative when you need all matching rows rather than a single result. By contrast, relying solely on XLOOKUP or INDEX‑MATCH with wildcards can produce misleading outcomes when duplicates exist, since they silently stop at the first match. Therefore, he recommends choosing FILTER or a dynamic array approach when the data may contain several valid matches or when you need to preserve all matching records.

Tradeoffs: simplicity versus precision and performance

While wildcards reduce formula length and improve readability, Farré also warns about tradeoffs. Wildcard patterns can produce false positives, for example matching an unwanted substring inside a larger word, and they require careful pattern design to avoid mistakes; thus, using them trades strict precision for flexible convenience. In addition, on very large datasets, broad wildcard searches can be slower than targeted comparisons, so users must balance the benefit of shorter formulas against potential performance costs.

Practical guidance and best practices

To mitigate the risks, Farré offers practical tips such as anchoring patterns with additional text, testing patterns on a subset of data first, and using the FILTER function when multiple results are expected. He also reminds viewers to escape wildcard characters when they are part of literal text to prevent accidental matches, and to be mindful of case sensitivity and data types that might affect results. Overall, his guidance centers on choosing the right tool for the job: use wildcards for flexible text matching, and prefer deterministic or row-level methods when exactness or speed matters most.

Why this matters for Excel users and teams

In the broader context, Farré’s video emphasizes maintainability and collaboration: shorter, clearer formulas make spreadsheets easier to audit and update, which benefits teams that inherit others’ work. Moreover, as Excel continues to add dynamic array functions and performance improvements, combining wildcards with FILTER and other modern functions offers a compact way to produce complex results without convoluted expressions. Consequently, adopting these patterns can reduce errors and speed up common analysis tasks when applied thoughtfully.

Bottom line

Kenji Farré’s tutorial is a concise reminder that often the simplest tools are the most effective. By demonstrating how the * and ? can replace long formulas across COUNTIFS, SUMIFS, Find & Replace, Filter, XLOOKUP and INDEX‑MATCH scenarios, he makes a practical case for cleaner, easier-to-read spreadsheets. However, he also makes it clear that users must weigh convenience against the need for precision and performance, and that choosing between single-result lookups and multi-row filters depends on the specific requirements of each dataset.

Keywords

excel wildcard tips, use * and ? in excel, simplify formulas with wildcards, excel wildcard search, replace long formulas excel, google sheets wildcard guide, vlookup with wildcards, text matching with * and ? in excel