Today’s function is FLEXIBLE_FILTER. It returns an array of values based on multiple partial text searches.

 Today’s function is FLEXIBLE_FILTER. It returns an array of values based on multiple partial text searches.

=LAMBDA(returnArray,lookupArray,searchStrings,[delimiter],[matchEntireCell],[matchAllSearchStrings],[ifEmpty],
IF(matchEntireCell,
FILTER(returnArray, BYROW(ISNUMBER(XMATCH(lookupArray, TEXTSPLIT(searchStrings, IF(ISOMITTED(delimiter), ",", delimiter)))), IF(matchAllSearchStrings, AND, OR)), ifEmpty),
FILTER(returnArray, BYROW(ISNUMBER(SEARCH(TEXTSPLIT(searchStrings, IF(ISOMITTED(delimiter), ",", delimiter)), BYROW(lookupArray, CONCAT))), IF(matchAllSearchStrings, AND, OR)), ifEmpty)))