How to reference another sheet in the QUERY function?

Question

In short, this doesn't work:

=QUERY(SHEET1!A:E, "where 'SHEET1'!B contains '"&A1&"'")

I am shown:

unable to parse query string

and I can't work out why.

Answer 1

You need to use column letters for your query, not a sheet reference. If you are looking for a value in column B, then use B in your query.

=QUERY(SHEET1!A:E, "select A,B,C where B contains '"&A1&"'")

This will select column A, B, and C where column B contains your query. Without the select portion, it will select the first column in your range, in this case column A.