The FILTER function does not supportwildcards, so we need to use a different approach.
If SEARCH finds a result, it returns the position of that result in the text.
For example:
If SEARCH doesn’t find anything, it returns the #VALUE!
error:
In other words, if SEARCH returns a number, we have a match.
If not, we don’t have a match.
To convert this result into a simple TRUE/FALSE value, we wrap the SEARCH function inside theISNUMBER function.
First, if the FILTER function does not return any match, it will return a #CALC!
Finally, we need to handle the case of the search text in H4 being empty.
This is a form ofBoolean logic.
The math operation ofmultiplication (*) works like AND logic in array formulas.
Partial match with INDEX and MATCH
The FILTER function is available only in Excel 365.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
SEARCH returns the position offind_textinsidewithin_textas a number.
