Lookup & Reference
Extract All Partial Substring Matches Into a Dynamic List (FILTER & SEARCH)
Return every row whose description or SKU contains a partial search string using row-level ISNUMBER(SEARCH(...)) flags paired with FILTER, replacing fragile legacy INDEX/SMALL array formulas.
Why VLOOKUP and XLOOKUP stop at the first partial match
Wildcard lookups such as VLOOKUP("*" & key & "*", table, col, FALSE) or XLOOKUP("*" & key & "*", col, ret, "", 2) stop at the very first matching row. When searching a catalog for every item containing "optic" or "fiber", analysts need all matching rows to spill into a clean report block.
Adding an auditable boolean flag column C2:C6 with =AND(LEN(TRIM($F$2)) > 0, ISNUMBER(SEARCH(TRIM($F$2), B2))) tests each description case-insensitively while guarding blank search boxes so an empty query does not accidentally dump the entire catalog. Passing C2:C6 into FILTER(A2:B6, C2:C6, "No Matching Items") spills all matching SKUs and descriptions automatically.
| SKU | Part Description | Matches "Fiber"? | Returned in Spill? |
|---|---|---|---|
| NET-101 | Single-Mode Fiber Patch Cord | TRUE | Yes (Row 1 of spill) |
| NET-102 | Cat6A Shielded Copper Cable | FALSE | Excluded |
| NET-103 | LC Duplex Fiber Transceiver | TRUE | Yes (Row 2 of spill) |
| NET-104 | Rackmount Patch Panel 24-Port | FALSE | Excluded |
| NET-105 | Armored Outdoor Fiber Trunk | TRUE | Yes (Row 3 of spill) |
Row-Level Case-Insensitive Substring Flag & Dynamic FILTER
Flags whether B2 contains the trimmed search term in $F$2 (returning FALSE when $F$2 is blank), then spills or joins all matching SKUs cleanly.
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, C2:C6, "No Matching Items"))Count of Partial Substring Matches
Counts how many catalog rows match the active partial keyword before rendering or paginating results.
=IF(LEN(TRIM(F2)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(F2) & "*"))Try the example
Use the original values or change them to test your understanding.