Excel: New FILTER + Wildcards Tricks

Key insights

• FILTER: Excel’s dynamic-array function that returns matching rows or columns.
  Use syntax =FILTER(array, include, [if_empty]) and rely on its spill behavior to keep results live when source data changes.
• wildcards (asterisk * and question mark ?): traditionally used for partial text matches in Excel’s UI and some lookup functions.
  FILTER does not accept wildcards directly in the include argument (for example, range="*text*" will error).
• SEARCH + ISNUMBER technique: combine these to mimic wildcards inside FILTER.
  Example: =FILTER(A2:A100, ISNUMBER(SEARCH("Sponsor 1", V2:V100))) filters rows where V contains “Sponsor 1”.
• BYROW, XMATCH and REGEXTEST: use these newer functions for more advanced, flexible pattern matching and row-wise evaluations.
  REGEX-based tests handle complex patterns; BYROW lets you apply logic across each row before filtering.
• Legacy filters and lookups: use AutoFilter, Advanced Filter, or lookup functions like XLOOKUP and VLOOKUP when you need built-in wildcard support in dialogs or simple lookups.
  These remain useful for manual filtering and quick partial matches.
• Practical tips: prefer SEARCH/ISNUMBER for simple partial matches and REGEX for complex patterns; include [if_empty] to handle no-match cases.
  Test formulas on sample data and avoid direct wildcard syntax inside FILTER’s include argument to prevent errors.

Introduction

The newsroom reviewed a recent YouTube tutorial by Excel Off The Grid that demonstrates modern techniques for using Excel’s FILTER function together with wildcard-like matching. In the video, the creator shows step-by-step alternatives because direct wildcards are not supported inside the FILTER function’s include argument. Consequently, the author demonstrates several formulas and compares them to establish which approach works best in practice. This article summarizes those methods and highlights the tradeoffs for editors and spreadsheet users.

First, the video frames the problem clearly: users often want to return rows that contain a partial text match, such as anything containing a brand name or keyword. However, typing a pattern like *text* directly inside FILTER produces errors, so creators must combine functions to mimic wildcard behavior. Therefore, the presenter evaluates both simpler functions and more advanced tools that became available in recent Excel updates. The goal is to pick methods that balance accuracy, speed, and ease of maintenance.

What the video demonstrates

The tutorial walks through multiple examples, starting with basic partial matches and moving to more complex scenarios such as continuous and discounted cash flows. For each scenario, the presenter applies different function combinations and shows the results live so viewers can follow the spill behavior and intermediate arrays. In particular, the video highlights combinations involving SEARCH, BYROW with XMATCH, and the newer REGEXTEST function when it’s available. As a result, the audience sees both the mechanics and practical consequences of each choice.

Moreover, the video explains how errors and special cases affect results, and then suggests small adjustments to guard against empty returns or mismatches. For example, the presenter demonstrates how to wrap a default value into a FILTER using its optional argument, which keeps sheets user-friendly when no matches exist. In short, the tutorial mixes live examples with defensive coding practices. Thus it helps viewers adopt robust filters without breaking spreadsheets.

Techniques explained

One commonly recommended approach is to combine FILTER with ISNUMBER(SEARCH(...)) so Excel treats partial matches as logical include arrays. Because SEARCH returns a position when it finds text, wrapping it with ISNUMBER yields TRUE for matches and FALSE otherwise. Consequently, this method recreates the effect of a *text* pattern inside FILTER while preserving dynamic spill behavior. It also works across many Excel versions without requiring regular expression knowledge.

Another approach the video covers uses BYROW in combination with XMATCH to test multiple patterns per row. This technique excels when you need to check several possible substrings at once and then filter by any positive result. In practice, it scales well for short lists of patterns, but it can get complex if you try to test dozens of patterns inline. Therefore, the presenter suggests using helper ranges for clarity when pattern lists grow large.

Finally, where available, the presenter recommends using REGEXTEST for powerful pattern matching. Regular expressions allow precise control over anchors, groups, and optional tokens that simple wildcard logic cannot express. However, while REGEXTEST simplifies many tasks, it imposes a steeper learning curve and may reduce portability between Excel builds that lack the function. Thus, it suits advanced users but may complicate collaborative work.

Tradeoffs and challenges

Each method involves tradeoffs between ease of use, performance, and compatibility. For instance, ISNUMBER(SEARCH) uses simple functions that most users already know, but it performs a text scan for each row and pattern, which can slow large workbooks. In contrast, REGEXTEST offers terse expressions and often faster matching semantics, yet it demands regex literacy and may not exist in older Excel versions. Therefore, choosing a method depends on the dataset size, team familiarity, and version constraints.

Another challenge arises when formulas return errors or unexpected blanks, especially if the source contains inconsistent data types. Although the tutorial shows error-handling wraps and default results for empty filters, these safeguards add complexity. Moreover, array-heavy formulas can make debugging harder for users who prefer stepwise helper columns. Consequently, the author balances concision against maintainability and recommends clear documentation of any advanced formula choices.

Performance also matters when filters form part of dashboards or automated reports. Dynamic array formulas update in real time, so inefficient pattern tests can create visible lag. The presenter therefore advises testing approaches on representative data and considering helper columns or aggregate queries if filtering becomes a bottleneck. In this way, practical constraints guide the selection between flexible formulas and robust, maintainable solutions.

Practical advice and conclusion

In conclusion, the video from Excel Off The Grid provides a useful, practical guide to recreating wildcard matching inside the FILTER function. For most teams, the recommended starting point is ISNUMBER(SEARCH) for its simplicity and broad compatibility, while advanced users may prefer REGEXTEST or BYROW plus XMATCH for more control. Importantly, the creator emphasizes testing and documenting whichever method you pick to keep spreadsheets reliable and understandable.

Ultimately, the choice between readability, speed, and expressive power depends on the specific task and the Excel environment in use. Therefore, readers should try the methods on a copy of their workbook, measure performance, and choose the simplest approach that meets their requirements. Through clear examples and comparisons, the video helps users make an informed decision and adopt modern, dynamic filtering techniques confidently.

Keywords

FILTER function Excel, Excel FILTER wildcard, FILTER and wildcards in Excel, dynamic array FILTER Excel, advanced FILTER techniques Excel, use wildcards with FILTER Excel, FILTER wildcard examples Excel, Excel FILTER tips and tricks