I would like to use the Filter function with wildcards, as shown below. I have viewed this question (although it pertains to Google Sheets) and tried it but I failed. How can i make it work?
2 Answers
Your formula is pretty close to working. What makes it fail is that SEARCH returns #VALUE when it does not find "Apple" in Banner C. Just wrap your SEARCH in an IFERROR:
=FILTER(D7:E9,IFERROR(SEARCH("Apple",D7:D9),0))
Edit
As mentioned by @Ben Knight, there is no need for wildcards here, since you are searching for a single word. "Apple" work the same as "*Apple*". If you wanted to find Apple(whatever)Cucumber in Apple Banana Cucumber, you would search for "Apple*Cucumber".
You can do without the IFERROR by using an ARRAYFORMULA+ISNUMBER+SEARCH combination:
=filter(D7:E9,arrayformula(isnumber(search("Apple",D7:D9,1)))=true)
No need to use wildcards either; just word "Apple" itself will be enough.
Related