Search code examples
google-sheetsfilterconcatenationarray-formulasgoogle-sheets-query

Filtering a Sheet across multiple columns

Looking for some help with a filtering problem. I have a sheet with dozens of columns, any one of which may contain data a user might want to filter on. For usability sake, what I want to do is create a filter tab that has some empty cells a user can populate and be returned a list of all the rows that contained all the search terms.

Example: enter image description here

The data in this example has rows 1, 2 and 3. Based on the selection criteria (Denver, Product 1 and Skill 1), only the 2nd row contains all three search terms.

Is there a filter function what will search across rows and columns?

https://docs.google.com/spreadsheets/d/1nMu00tyCC0gEw4IPuaXG1nb50Xj7IYQ9Gcmq6C7xgfs/edit?usp=sharing

Solution

  • try:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(QUERY({A7:J9, 
 TRANSPOSE(QUERY(TRANSPOSE(A7:J9),,99^99))}, 
 "where "&JOIN(" and ", IF(B1:B3="",,
 "lower(Col"&COLUMN(J9)+1&") contains '"&
 LOWER(B1:B3)&"'")), 0), 99^99, COLUMNS(A7:J9)))

    0