0

I have a sheet with data, theres a section laid out 8x9 with a layout approximately similar to below (didn't recreate all 9 rows):

Red Orange Yellow Green Blue Indigo Violet Grey
X - X - - - - -
X X X - - - - X
- X X - - X - X

I am trying to set up a front page where my less savvy colleagues can access specific data from other columns (not these shown) based on their requirements.

So, from a range of 8 dropdowns (or checkboxes) that correspond to the headers above, my colleague might select yellow and orange. In this example I would desire the return to be the second and third row of data as they both have yellow and orange.

I can't seem to figure out how to set something up to filter or query ONLY selected columns and ignore those that aren't indicated in dropdowns. Everything I've gotten to work has required an exact match of all columns being either true or false. I need to search or query only the selected columns.

Am I making sense?

Improve this question
6
  • Hi and welcome. Would you please shgow an exampleof a formula that you have working, even though it does not meet your requirement. Would you also clarify 1) that in the sample table at the top of your question, "X" substitutes for a value (any value) as distinct from a null value, and 2) are the values strings or numbers?
    – Tedinoz
    Sep 13, 2021 at 19:54
  • Have you read Dynamically query or filter a table in Google Sheets using checkboxes and searchbox on StackOverflow? This would seem to reflect your scenario (though yours is on a bigger scale).
    – Tedinoz
    Sep 13, 2021 at 20:05
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there.
    – doubleunary
    Sep 14, 2021 at 5:00
  • @Tedinoz Yes, I saw that page. It was definitely the closest to what I am attempting. However I am not knowledgeable enough (yet) to apply it to my scenario. Per the other suggestion I created a sample sheet. I greatly appreciate everyone's patience. I included my attempt as well as what I would like the result to look like. I think this may explain the other questions regarding X and value strings. The intent was to indicate a check box or TRUE value. docs.google.com/spreadsheets/d/…
    – P.Hall
    Sep 14, 2021 at 17:51
  • @doubleunary Thank you, and see the response able with the link.
    – P.Hall
    Sep 14, 2021 at 17:52

2 Answers 2

Reset to default
1

Given that there are a few criteria columns, you may want to try the iferror(filter(not(mmult(sign(not()))))) pattern, like this:

=iferror( 
  filter( 
    'Sample Dataset List'!A2:B, 
    not( 
      mmult( 
        sign( not( iferror( 
          search(D8:K8, 'Sample Dataset List'!C2:J), 
          (D8:K8 = "-") + (D8:K8 = "") 
        ) ) ), 
        transpose(sign(column(D8:K8))) 
      ) 
    ) 
  ), 
  "(no matching data)" 
)

The formula will show all rows where all search keys appear as part of the value in the relevant dataset column. The search is case-insensitive.

The (D8:K8 = "-") + (D8:K8 = "") bit makes the formula assume that blank search keys and search keys like - signify that any value is acceptable in the relevant column in the dataset.

See cell B15 in the sample spreadsheet for an illustration.

See the Elegant search spreadsheet for another example of how to apply the pattern. Click the get your own copy link to test it privately.

Improve this answer
4
  • This looks like it might work. Investigating . . . :)
    – P.Hall
    Sep 14, 2021 at 18:08
  • Beautiful, thank you! If I were to add additional rows of data on the 'Sample Dataset List' page, all I need is to change the cell ranges?
    – P.Hall
    Sep 14, 2021 at 20:15
  • The formula uses open-ended references so it will handle any number of rows. You only need to modify the ranges if you add columns.
    – doubleunary
    Sep 14, 2021 at 20:25
  • Beautiful! Thank you so much!
    – P.Hall
    Sep 14, 2021 at 21:37
0

Dynamically query or filter a table in Google Sheets using checkboxes and searchbox deals with the same scenario as the OP, though with only two selection columns.

This answer is an adaptation to suit the OP (with 8 selector columns).

=query({'Sample Dataset List'!A2:J},"select Col1, Col2 where 0=0"&IF($D$8="Yes"," and Col3='Yes'","")&IF($E$8="Yes"," and Col4='Yes'","")&IF($F$8="Yes"," and Col5='Yes'","")&IF($G$8="Yes"," and Col6='Yes'","")&IF($H$8="Yes"," and Col7='Yes'","")&IF($I$8="Yes"," and Col8='Yes'","")&IF($J$8="Yes"," and Col9='Yes'","")&IF($K$8="Yes"," and Col10='Yes'",""))

results

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.