Forum Discussion

NSUMike's avatar
NSUMike
Copper Contributor
Jan 10, 2023
Solved

FILTER with SEARCH Function Question

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 

 

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

 

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. 

  • 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})

     

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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})

     

    • NSUMike's avatar
      NSUMike
      Copper Contributor
      Fantastic! That worked like a charm. I learn a little bit more every day. Thank you so much.

Resources