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 want to search for "Asset" but want to know all the terms where "Asset" is included, e.g., "Asset Management" would show up in my results as "Asset" is included in the term. I don't want to have to use FIND every time I need to search. I tried to set it up using the FILTER function, but I must not understand how it works, as I continue to get errors. Can you please suggest a way to best approach this? Is the FILTER function the best way, or can you suggest another option? Thank you.
6 Replies
If you're using Excel 365, FILTER works well for this. For example, if your list is in A2:A100 and your search term is in D1, try:
=FILTER(A2:A100,ISNUMBER(SEARCH(D1,A2:A100)),"No matches found")
This will return every entry containing the text in D1, so searching for Asset would return Asset Management, Asset Tracking, etc. SEARCH is case-insensitive, which is usually helpful for this kind of search.
Since you have FILTER you may well have the REGEX functions.
Worksheet formula = FILTER(list, REGEXTEST(list, term))The above will examine each member of the 'list' to determine whether it contains a match for the 'term'.
To cater for missing terms requires the [if_empty] optional parameter to be provided
Worksheet formula = FILTER(list, REGEXTEST(list, term), "Not present")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.
I'm not sure FILTER is the best approach for this but maybe, depends on more details and how you have the sheet set up and what you need. That said here is the basic structure of how to set up the FILTER to do this.
Basically you want to use ISNUMBER(SEARCH("Asset", [array])) as the conditional to see if "Asset" is found anywhere in each of those elements. So the format would look like:
=FILTER( [Table], ISNUMBER(SEARCH("Asset", [array])), "None Found")
That said I might suggest using the Quick Filter option to add the drop downs on each column (or Format as Table) and then under the drop down you can easily search for "Asset" in the search box and filter the table accordingly.
The purpose of this list of words is to identify common words/terms and their definitions used in our Quality Management System documents. We are working on a system that simplifies our procedures where the reviewer can refer to another document to provide necessary definitions. Basically, we are removing definitions from our documents and instead have ONE document that provides all definitions. This allows for consistency within our documentation as well as ensuring that our definitions across our organization meets regulatory requirements. In order to ensure that we have captured all definitions, we are reviewing each document and comparing included definitions to the list of words to see if that word/term is already included in our list. I am trying to use the FILTER function in conjunction with setting up a search bar so that I can just enter a word or term in the search bar, rather than going to FIND and scrolling through the list, and have Excel search the list and come back with a list where that word/term is included. For example, let's say my list includes: Asset, Asset Management, Asset Negotiations, Liability, Liability Management. I want to be able to enter the word "Asset", in the search bar and have Excel provide a separate list that would include: Asset, Asset Management, and Asset Negotiations, or enter a part of the word, e.g., "Aud" and have it come back with Audit, Auditor, etc. Can this be done?
First off, being in Quality and Regulatory for medical device companies, I feel your pain and commend your efforts.
That said, my prior post still applies the best that I can tell. Here is an example using the 'quick filters':
notice how I typed "Process" in and only the 2 lines are shown in the box below it and if I click OK the list is filtered to only those 2 lines
And here is an example using the FILTER function:
notice how that returns a separate list (could be on a different tab if you want) as opposed to filtering the view on the current list. You can also see in the formula bar I tweaked the formula to search for the search term in E1 to be found in EITHER column A or column B. Note I used the perior (.) character after the colon in the ranges to tell excell to only use the used range in that area.
If you instead define the original data as a Table (Home -> Format as Table) then you can use table references instead of cryptic ranges. Let's say you name the Table: "tblTerms" then it would look like:
=FILTER(tblTerms, ISNUMBER(SEARCH(E1, tblTerms[Term]))+ISNUMBER(SEARCH(E1, tblTerms[Definition])), "none found")
like this:
I hope this is helpful.
