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.

  • 1
    Welcome. Please share a test sheet. – marikamitsos Dec 17 '19 at 9:55

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)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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