How can I filter my search in a column on google sheet which collects data from a form?

Question

I would like to perform a multi criteria search of data in a column- contains data of check boxes(more than one option chosen).

For a clearer picture of what I am trying to do, screenshot below is a question in a form

Data from the form are saved in sheets like below,

So my concern here is if I would like to search/filter for the rows that contain "Commercial", the rows with Commercial,Engineering doesn't show up. That's definitely not an effective search.

Any advise on how can I go about this issue is kindly appreciated. If

Answer 1

Let's say you have your form in the response sheet in columns A to P, with the multiple choice in col D. If you want to filter your data on the word 'Commercial' you can either do:

=filter(A2:P, regexmatch(A2:P, "Commercial"))

or use query():

=query(A2:P, "select * where B contains 'Commercial' ")

Note: depending on your locale you may have to change the commas to semi-colons in order for the formulas to work.

I hope that helps ?