Query or Filter formula to 'search for' and 'return' multiple words

Question

I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet.
The filter I have created is working but not exactly how I want it! The column I am referring to has words such as:

• Solar
• Energy
• Battery

and sometimes may have two words in the cell such as:

• ENERGY COMPARISON, SOLAR (see image below)
  and these words are separated by a comma ,

The formula I created recognizes the single words. However it does not bring over the words that belong to a cell with multiple words.

The example formula I am using that works is this:

=filter('Form responses'!A:A,'Form responses'!I:I="Solar")

How do I update it to also capture the multiple words?

Thank you! :)

Answer 1

Following the OP's Edit things are quite clearer.

---

Using the FILTER function

=FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar"))

Extra tip
If you were to use the | (pipe symbol) in the FILTER function which stands for OR, then the formula would change to:

=FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar|Energy Comparison"))

But you don't really need to because the first condition (Solar) already includes all text that has the word Solar.

---

Using a QUERY

=QUERY('Form responses'!A:I, "select A where I matches '.\bSolar\b.' ",0)

As before, it is not necessary to use the | (pipe)

=QUERY('Form responses'!A:I, "select A where I matches '.\bSolar\b.|.\bEnergy\b.' ",0)

Answer 2

Note that: results given by the following provided formula are highly dependent on the way you have built your form.
If for any reason it does not work for you, please -as mentioned by others- share a link to a test spreadsheet with dummy data (John for Mark, Mr Green for Mr Brown).

---

The query formula using a case-insensitive regular expression (regex) for your search will give the results you want.

=QUERY('Form responses'!A2:C200, ""select * where lower(A) matches 
       '.\bsolar\b.|.\benergy\b.'")

Brief explanation (of what the query "says")

We assume your data starts at column A, row 2, in the spreadsheet Form responses ('Form responses'!A2:C200)

Choose the range A2:C200 and return all the columns wherever -in your search within column A- you find results matching the case-insensitive words solar or SOLAR or even sOlaR or energy or Energy etc, no-matter how many or what characters are before or after them.

• *: everything (all columns in the range)
• lower(A): make characters case-insensitive
• .*: . any character, * any number of times
• \b: word boundaries (prevents from matching subsolar or solarized)
• |: (pipe) or