digest
Short Summary
Main recommendation: Use the FILTER function with a SEARCH-based TRUE/FALSE array to extract table records whose text contains a partial criterion, such as "quad" or "Carlota," in a chosen column. The approach works by using SEARCH to find the text, ISNUMBER to convert the results to TRUE/FALSE, and FILTER to return only the matching rows.
Key Points
- SEARCH returns the starting position of the search text when found and an error when not found, so ISNUMBER turns those results into TRUE (found) or FALSE (not found).
- The FILTER function takes the whole table as its array and the TRUE/FALSE array as its include argument, and it spills the matching rows automatically.
- Because the formula lives only in the top-left cell, the spilled results update when the criterion cell (defined as "partial text criteria") changes.
- SEARCH is case-insensitive, so a lowercase "c" matches uppercase "C"; FIND is case-sensitive and can be used when case must be enforced.
- The same method works for partial text in other columns, demonstrated with customer addresses such as "San Pablo" and "College Ave."
Details Worth Noting
- Concrete instructions:
- Define a cell name (for example, "partial text criteria") for the cell holding the search text.
- Build the TRUE/FALSE array with =ISNUMBER(SEARCH(partial_text_criteria, product_column)), selecting the whole column rather than a single cell.
- Press F9 while editing the formula to preview the array and confirm it shows TRUE for matches and FALSE otherwise.
- Enter =FILTER(table_name, ISNUMBER(SEARCH(partial_text_criteria, product_column))) in a cell, selecting the entire table by clicking the diagonal arrow in the upper-left corner, then press Enter.
- To enforce case sensitivity, replace SEARCH with FIND in the same formula.
- To filter by another column, such as addresses, change the column referenced in SEARCH to that column.
- The speaker notes that searching for "C" alone returns all "Carlota" records plus records such as "Claude," "trick," and "catch," showing that broader criteria return broader results.
- In SEARCH, a found match returns a position number (for example, 1 for "Carlota" at the start of a name, 9 for a "C" found in the word "Claude").
- The FILTER function requires Office 365 or a version that supports it.
Bottom Line
Combining SEARCH, ISNUMBER, and FILTER gives a reliable, updatable way to pull out records by partial text match, and switching to FIND adds case sensitivity when needed.
