SOLVED

FILTER with SEARCH Function Question

New Contributor

Good day, 

 

I have a spreadsheet with two tabs. In one tab (called "QUERY"), I have the dataset I'm working with, which is coming from Azure Devops via the AzureDevops "Team" integration. So anytime I refresh, the data is likely to change. The table for that dataset is called "VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1."

 

In the other tab (called "RIs"), I'm using the FILTER function to pull in rows of data in which a specific keyword exists in the "Tags" column in the table referenced above. 

 

I have been successful in pulling in all columns of data (A thru E) for a row where I have a keyword match using this formula: 

=FILTER(VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1, ISNUMBER(SEARCH("Reviewed",VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[Tags])))

 

What I have not been able to figure out is how to pull in only a few columns (A thru C). In other use cases I have used a slightly different FILTER formula to gather columns (A thru C), but not with using the SEARCH function to find partial matches for a keyword, but rather matching a "State." 

=FILTER(QUERY!A:A:QUERY!C:C,((QUERY!D:D="Review Complete")))

 

My question is if it's possible to use the search and filter functions combined to display only the first 3 columns when a keyword is found in the "Tags" column. 

 

Screen shots below. 

Image 1 - (Tab: "QUERY") dataset coming from AzureDevops 

NSUMike_0-1673388229450.png

 

Image 2 - (Tab: "RIs") table in the tab that I am importing that data into using the filter function. 

NSUMike_1-1673388306157.png

 

I don't post often, so I'm hoping I provided enough information to make sense of my request. 

Appreciate any guidance you can provide. 

 

Thank you. 

2 Replies
best response confirmed by NSUMike (New Contributor)
Solution

@NSUMike When you select the entire table as the filter array, all columns will be returned by FILTER. Extend the array code by the first and last column you want to display for.

 

In you case that would become:

VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[[ID]:[Full Name]]

 

If you would want to return only the first and third column, use your original formula but wrap it in another FILTER adding a "mask" of columns that should be returned between curly brackets. Any number not equal to zero=include, 0=exclude). Something like this:

 

=FILTER(FILTER(..................),{1,0,1,0,0})

 

Fantastic! That worked like a charm. I learn a little bit more every day. Thank you so much.