CODEWITHSUNDEEP
parsinggoogle-sheetssyntaxgoogle-sheets-query
Jack
Jack

Reputation: 361

How to reference another sheet in the QUERY function?

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.

Upvotes: 2

Views: 7297

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18727

No way. You need to paste data from Sheet1 B:B to the current sheet, and then query it. Use formula:

=ArrayFormula('SHEET1'!B:B)

or combine data inside query formula:

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

If all the data is placed in one sheet, just use basic query syntax:

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

Upvotes: 0

Douglas Gaskell
Douglas Gaskell

Reputation: 10100

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.

Upvotes: 1

Related Questions