Forum Discussion
Using the FILTER Function
I have a spreadsheet with a long list that I need to search to determine if a specific word or term is included in the list. In some cases, I want to search for part of the word or term, e.g., I may...
Hello Sue_G,
Yes, this can be done with FILTER together with SEARCH.
If your list of terms is in A2:A1000 and your search cell is E1, you can use:
=FILTER(A2:A1000,ISNUMBER(SEARCH(E1,A2:A1000)),"No matches found")
SEARCH looks for the text anywhere within each entry, so entering "Asset" would return Asset, Asset Management, and Asset Negotiations. Entering "Aud" would return matches such as Audit and Auditor.
If your data is stored as an Excel Table, you can also use structured references:
=FILTER(tblTerms[Term],ISNUMBER(SEARCH(E1,tblTerms[Term])),"No matches found")
Using a Table has the added benefit that the formula automatically includes new terms as the list grows.