Trying to filter out substring from another sheet in google-spreadsheet (edit: Now with only SOLUTION/FORMULA condensed to one Sheet )

Question

Problem Description:

• I have a master-Sheet which is fed by incoming information from a google-form
• It contains sensitive information like phone numbers so I have imported it to another workbook with only desired details like Date,Member-Name and College-Name

I went with the rather simplistic approach of importing like this: =Importrange("MY_SHEET_KEY","Sheet1!A1:H1024")

• On the other sheet of the same workbook, I have attemped this and failed: =filter(Sheet1!A1:H1024,Sheet1!E1:E1024="*"&"National"&"*")

I actually want to filter out "National College of Information Technology" from a list of colleges with the formula given above.

FOR REFERENCE:

this works:

=filter(Sheet1!A1:H1024,Sheet1!E1:E1024="NO")

however, this FAILS to work:

=filter(Sheet1!A1:H1024,Sheet1!E1:E1024="*"&"N"&"*")

Please give me an example of a formula that allows me to filter the students of "National College of IT", "Himalayan College of IT" or "St Lawrence College". I think finding sub-string "National", "Himalayan" and "Lawrence" is quite sufficient for my purposes.

I have referred several posts in stackoverflow to hit nothing what am I doing wrong?

EDIT1:

Okay I managed to overcome the filtering process bu using Query() function. However google-sheets query seems to be case sensitive. I am trying to find a work-around.

For example:

This works:

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where (C contains 'NCCS' OR C contains 'National') ")

but it FAILS to show me if the field has entry in the form of "nccs" or "national college". I would like it to disregard case and process whatever case people throw at it.

Right now, if the college name contains "NCCS" or "National" as sub-string, only then, it is working. And it is also Case-sensitive.

EDIT2:

The solution given by Ed Nelson was perfect for my current problem! Thanks a lot!

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

---

NEW PROBLEM

I am now modifying the question to address a new problem

• The solution provided so far has managed to address problems with data filtering with two Sheets(Sheet1 and Sheet2)
• Sheet2 acquires data from master-Sheet from another google-workbook
• This is done so that sensitive information like Phone number is filtered out
• I am supposed to SHARE these sheets with people and I would not like to give out more information than necessary

• My current setup allows me to do this by applying this formula in Sheet1

  =Importrange("MY_SHEET_KEY","Sheet1!A1:H1024")

• and this formula in Sheet2

  =query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

• However, as I am sharing a workbook, those who get the link are easily able to see Sheet1 also, which contains information on everyone without filtering.

• I would like to know how these two formulas can be condensed into just one formula for a single spreadsheet (Note: My previous similar attempt with filter() has landed me in a recursive definition warning and it failed to process.)

Answer 1

To overcome the case problem try:

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

Yes, ImportRange and Query can be combined. Note in the query Col refeferences must be used. Column C is Col3.

=query(Importrange("MY_SHEET_KEY","Sheet1!A1:G1024"),"select * where lower(Col3) contains lower('NCCS') OR lower(Col3) contains lower('National')")

Answer 2

NEW PROBLEM ANSWER. Yes, you can combine importrange and query. Note that the columns are referenced by Col3 (equals C). This seems to be required.

=query(Importrange("MY_SHEET_KEY","Sheet1!A1:G1024"),"select * where lower(Col3) contains lower('NCCS') OR lower(Col3) contains lower('National')")