Excel Wildcard Characters: Complete Guide to Asterisk, Question Mark, and Tilde

Excel wildcard characters are special symbols that let you perform partial text matching inside formulas, filters, and Find & Replace dialogs. The three core wildcards are the asterisk (*), the question mark (?), and the tilde (~), and together they unlock pattern matching that would otherwise require complex nested formulas or VBA. Whether you're searching a customer list for everyone whose surname starts with "John," or filtering invoices that contain a specific product code, mastering these symbols turns hours of manual scrolling into a single, elegant formula.

The asterisk represents any sequence of characters, including no characters at all. The question mark stands in for exactly one character, while the tilde acts as an escape character when you need to search for a literal asterisk or question mark inside your data. These three symbols work consistently across dozens of Excel functions, including SUMIF, COUNTIF, AVERAGEIF, SEARCH, MATCH, and the ever-popular vlookup excel function when used creatively with text concatenation.

Understanding when wildcards work and when they fail is just as important as knowing the syntax. Wildcards function inside text-comparison functions but do not work inside SUM, INDEX, or direct equality checks like =A1="Jo*". They also behave differently between Excel for Windows, Excel for Mac, and Excel for the Web, particularly when combined with array formulas or the newer dynamic array functions like FILTER and XLOOKUP.

This guide walks through every wildcard scenario you'll encounter in real spreadsheet work. We'll cover syntax fundamentals, function compatibility, troubleshooting common errors, performance considerations on large datasets, and advanced patterns like combining wildcards with logical operators. By the end, you'll be able to write formulas that search 100,000-row datasets with surgical precision without ever leaving the keyboard or resorting to clunky helper columns.

If you're brand new to Excel pattern matching, don't worry — wildcards are among the most beginner-friendly advanced features in the application. The syntax is short, the rules are consistent, and the payoff is immediate. Most analysts report that learning wildcards properly saved them roughly two hours per week within the first month of regular use, and the productivity compounds as you build more sophisticated dashboards and reports.

We'll also touch on related skills that complement wildcard usage, such as conditional formatting, structured table references, Power Query text filters, and how wildcards integrate with the newer LAMBDA and TEXTSPLIT functions introduced in Microsoft 365. By treating wildcards as part of a broader text-manipulation toolkit, you'll start to see opportunities for them everywhere your data has even slightly messy or inconsistent formatting.

Finally, this guide is structured so you can either read top-to-bottom or jump directly to the section that matches your current problem. Use the table of contents below to navigate. Every section includes worked examples with formulas you can paste straight into your own workbook and adapt to your column references. Let's get started.

Wildcards truly shine inside lookup functions, and one of the most common questions Excel users ask is how to make vlookup excel formulas accept partial matches. By default, VLOOKUP requires an exact match (or an approximate match against sorted data), but if you concatenate wildcards into the lookup value, you can find rows where the key field merely contains your search term. The syntax looks like =VLOOKUP("*"&A2&"*", LookupTable, 2, FALSE), and it works beautifully for fuzzy customer name matching or product code lookups.

The MATCH function accepts wildcards in the same way and is often a better choice than VLOOKUP because it returns a position number you can feed into INDEX, allowing the lookup column to sit anywhere in the table. A formula like =MATCH("*widget*", A:A, 0) returns the row number of the first cell containing the word widget. Combine it with INDEX for a robust two-way lookup that handles partial matches without rebuilding your table layout.

XLOOKUP, the modern replacement for VLOOKUP introduced in Microsoft 365, has explicit wildcard support but requires you to set the fifth argument (match_mode) to 2. Without that argument, XLOOKUP treats your search string literally, including any asterisks or question marks you intended as wildcards. This trips up many users migrating from VLOOKUP, where wildcards worked silently as long as the fourth argument was FALSE.

HLOOKUP, the horizontal cousin of VLOOKUP, also supports wildcards using identical syntax. While HLOOKUP is less commonly used today because most analysts prefer tall, columnar data, it remains useful for financial models where data flows left to right across months. The same concatenation trick — wrapping your lookup value in asterisks — turns HLOOKUP into a partial-match search across column headers.

One important caveat: wildcards only work in lookup functions when the function is performing an exact-match search. If you accidentally leave VLOOKUP in approximate-match mode (the default fourth argument behavior), the asterisks and question marks will be treated as literal characters, and your formula will return wrong values silently. Always pass FALSE as the fourth argument when using wildcards with VLOOKUP, or use 0 with MATCH for the same effect.

Wildcards also work inside the newer FILTER function when combined with helper functions like SEARCH or ISNUMBER. The pattern =FILTER(Table, ISNUMBER(SEARCH("widget", Table[Product]))) acts like a wildcard contains-search and returns all matching rows as a dynamic array. This is arguably the most powerful wildcard pattern available in modern Excel because it scales to thousands of rows and updates automatically as your data changes.

For users on Excel 2019 or earlier without dynamic arrays, you can recreate similar behavior using INDEX, SMALL, and array-entered formulas, though the syntax is significantly more complex. If your organization has access to Microsoft 365, upgrading is worth it solely for the cleaner wildcard-driven filter patterns now available natively.

Beyond the basic functions, wildcards integrate with several advanced Excel features in ways that aren't always obvious. Conditional formatting accepts wildcards inside its formula-based rules, allowing you to highlight every cell in a range containing a specific substring. Create a new rule using "Use a formula," and enter =COUNTIF(A1, "*urgent*")>0 to highlight any cell with the word urgent. This pattern scales beautifully for color-coding status reports and project trackers.

Data validation also supports wildcard logic through custom formulas. While the built-in list and number constraints don't directly accept wildcards, a custom validation formula like =COUNTIF(ApprovedPrefixes, LEFT(A1,3)&"*")>0 can restrict entries to values starting with approved prefixes. This is particularly useful for enforcing naming conventions across collaborative workbooks where multiple users enter data into shared tables.

Pivot tables don't natively support wildcards in their filter dialogs, but you can replicate the behavior using slicers combined with a helper column. Add a column to your source data with a formula like =IF(ISNUMBER(SEARCH("widget", A2)), "Widget", "Other"), then filter the pivot table by that helper column. This pattern keeps your raw data clean while enabling sophisticated drill-down analysis without VBA.

Power Query, accessible through the Data tab, has its own wildcard-like text filters under the Filter Rows menu. While Power Query uses a slightly different syntax (Contains, Starts With, Ends With instead of asterisks), the underlying logic is identical. For datasets larger than 100,000 rows, Power Query often outperforms native wildcard formulas because it processes data in a compiled query rather than recalculating with every workbook change.

The SEARCH and FIND functions deserve special mention because they accept wildcards directly inside their first argument. SEARCH is case-insensitive and wildcard-aware, while FIND is case-sensitive and treats asterisks and question marks literally. Combining SEARCH with IFERROR creates a clean contains-search formula: =IFERROR(SEARCH("*widget*", A2)>0, FALSE). This returns TRUE when widget appears anywhere in A2 and FALSE otherwise, with no array entry required.

For users working with structured tables, wildcards work identically inside table references. A formula like =COUNTIF(Sales[Region], "*West*") behaves exactly the same as the equivalent A:A range version but updates automatically as new rows are added to the table. Table references combined with wildcards represent the gold standard for maintainable, scalable Excel formulas in 2024 and beyond.

Finally, the LAMBDA function introduced in Microsoft 365 lets you wrap wildcard patterns into reusable custom functions. You could define a named LAMBDA called CONTAINS that takes a range and a substring, then use =CONTAINS(A:A, "widget") anywhere in your workbook. This dramatically improves formula readability for analysts maintaining complex models, and it represents the future direction of Excel's text-matching capabilities.

Performance matters when using wildcards on large datasets. Excel evaluates wildcard patterns row by row, which means a COUNTIF with wildcards across a 500,000-row column will be noticeably slower than the equivalent exact-match formula. To keep workbooks responsive, restrict your criteria range to the actual data area rather than entire columns. Instead of A:A, use A2:A100000 or convert your data to a structured table so references update automatically as rows are added or removed.

Another performance trick is to use SUMPRODUCT with ISNUMBER and SEARCH for very large datasets because Excel's modern calculation engine sometimes optimizes these array operations better than COUNTIF on full-column references. Benchmark both approaches on your specific workbook — results vary by Excel version, available memory, and whether other volatile functions are present. The difference is often two to five times in either direction depending on your environment.

Wildcards combine powerfully with the IF function for conditional logic. A formula like =IF(COUNTIF(A2, "*error*")>0, "Review Required", "OK") flags rows containing error keywords for manual review. This pattern scales to thousands of rows and forms the backbone of many automated quality-control dashboards. Just remember that COUNTIF on a single cell returns 0 or 1, making it a clean substitute for ISNUMBER(SEARCH(...)) when you need a binary check.

Naming conventions matter when designing workbooks that rely on wildcards. Consistent prefixes, suffixes, and separator characters make wildcard patterns dramatically more reliable. If your team standardizes on patterns like INV-YYYY-NNNN for invoices and ORD-YYYY-NNNN for orders, you can build entire dashboards around simple wildcard queries that would otherwise require complex text parsing. Treat naming conventions as part of your data architecture, not an afterthought.

When wildcards aren't enough — for example, when you need true regex with character classes, lookaheads, or capture groups — consider using Power Query's M language or Office Scripts with TypeScript. Both environments offer regex support, and you can pipe their results back into your worksheet through queries or scripts. This hybrid approach lets you keep simple wildcard patterns in formulas while reserving regex for genuinely complex text-matching needs.

Documentation is the final piece of the puzzle. Wildcards make formulas terse and powerful, which can also make them cryptic six months later. Add a comment column or use the N function trick — =SUMIF(A:A, "*widget*", B:B)+N("Sums widget-related sales") — to embed inline notes that don't affect calculations. Your future self and colleagues will thank you when they revisit the workbook and need to understand exactly what each wildcard pattern is doing.

Mastering wildcards transforms how you interact with Excel data. Instead of building fragile helper columns or copying data to Python for text matching, you'll handle 90% of pattern-matching needs natively inside the spreadsheet. The investment to learn them is small — perhaps two hours of focused practice — and the payoff in saved time, cleaner workbooks, and analytical flexibility lasts an entire career.

To put everything together, let's walk through a practical workflow that uses wildcards from start to finish. Imagine you have a 50,000-row sales log with messy product descriptions, regional codes, and timestamps. Your goal is to produce a clean summary showing total revenue per product category and per region, where the category names are buried inside the description text and the regions are encoded with inconsistent prefixes. Wildcards make this otherwise painful task almost trivial.

Start by listing your target categories in a small reference table — perhaps Widget, Gadget, Sprocket, and Doohickey in column D. Next to each category, write a SUMIF formula like =SUMIF(Sales[Description], "*"&D2&"*", Sales[Revenue]). Drag this down, and Excel returns total revenue per category in seconds. The wildcard concatenation handles every spelling variation, capitalization difference, and embedded location detail without manual cleanup.

For the regional breakdown, suppose your region codes look like NW-Seattle-2024, SW-Phoenix-2024, and NE-Boston-2024. Use =SUMIF(Sales[RegionCode], "NW-*", Sales[Revenue]) to total all Northwest revenue regardless of city or year. Building a small matrix with cross-tabulated SUMIFS — region prefix on rows, category keywords on columns — gives you a complete pivot-style summary entirely through wildcard formulas, no actual pivot table required.

If your data includes special characters like asterisks in SKU codes (for example, SKU-*PREMIUM*-001), remember to escape them with tildes. A formula like =COUNTIF(Sales[SKU], "*~*PREMIUM~**") counts every SKU containing the literal substring *PREMIUM*. This is one of the most overlooked wildcard skills, and it's essential whenever your data uses asterisks or question marks as meaningful characters rather than wildcards.

For ongoing analysis, consider building a small dashboard with input cells where users can type a search pattern, and have formulas reference those cells with wildcards. For example, =COUNTIF(Sales[Description], "*"&$D$1&"*") creates a live search box that updates as users type. Pair this with conditional formatting and a few simple charts, and you have an interactive analytical tool that any colleague can use without knowing a single formula.

When troubleshooting wildcard formulas that return zero or unexpected results, the most common culprits are trailing spaces, hidden line breaks, and inconsistent capitalization. Wrap your criteria range in TRIM or CLEAN inside a helper column to neutralize these issues. You can also use the LEN function to spot rows with unexpected character counts — if a name should be 10 characters but LEN returns 12, there's almost certainly hidden whitespace breaking your match.

Finally, treat wildcard mastery as a stepping stone to broader text-manipulation skills. Once you're comfortable with asterisks and question marks, explore the TEXT, TEXTJOIN, TEXTSPLIT, and TEXTBEFORE functions for parsing and reassembling strings. Combine these with wildcards and the new dynamic array functions, and you'll handle text-processing tasks that previously required Python or VBA — all natively inside Excel, with formulas that any analyst can read, audit, and extend without specialized training.