Google-sheets – Sheets QUERY (Select, Where, Contains, Limit) that works, except when blank cell

google sheetsgoogle-sheets-query

I have a Google sheets QUERY formula that pretty much works:

=query(data,"SELECT A WHERE B contains '"& A5 &"' LIMIT 10",1)

Essentially what the query is doing is, for the given data range, it selects a cell from column A where column B contains certain text, and I am limiting the output list of values returned to just 10 rows. That all works fine.

Where I encounter a problem is when the "& A5 &" above references an empty cell, I am still getting a list of values returned (and the wrong ones) when really, if an empty cell is referenced, the output should be nothing at all. So, if the "& A5 &" references an empty cell, I want NO output values returned at all i.e. blank results.

I've done a cursory google search on several different forums for possible solutions. Apparently 'is not null' might be a solution? Or <> '' ? But I'm not sure how to incorporate into the syntax.

Apologies I'm not a sheets/query/syntax expert and would be grateful for advice/suggestions.

Best Answer

I am not skilled in query but I've had to solve similar problem and this syntax could work for you:

=query(data,"SELECT A WHERE B is not null and B contains '"& A5 &"' LIMIT 10",1)

Related Solutions

Google-sheets – Query multiple Google spreadsheets

Assuming that all your sheets are in the same spreadsheet and the range of interest is A:B where column B have values YES/NO and you want only the rows where B is equal to YES, then the following formula should work:

=QUERY({ListA!A:B;ListB!A:B;ListC!A:B}, "select Col1, Col2 where Col2='YES'")

References
QUERY - Docs editors Help

Google-sheets – Query Select Where is not null vs <>” not filtering properly

the query only grabs the headers.

No, it grabs some rows, too. Scroll down... way, way down. There they are. Here's why you are hitting this.

Some of your columns are completely empty. These are interpreted as string by default. Hence, is not null is always true for these, and you get the whole table.
Since you order by B (ascending by default), the huge number of empty rows ends up on top, and leads you to conclude that the query returns nothing.

Unfortunately, there seem to be no way to declare the type of an empty column. This, combined with the "no nulls in string column" behavior, leads to people being driven crazy by not null.

To keep your sanity, either

make sure every column has at least one nonempty cell, with some value of the type you expect in that column.
or, use the less powerful but more straightforward filter command when it suffices for the task. Note that the output of filter can be fed into a query, to combine the best features of both.

Best Answer

Related Solutions

Google-sheets – Query multiple Google spreadsheets

Google-sheets – Query Select Where is not null vs <>” not filtering properly

Related Topic