google-sheetsgoogle-sheets-formula

Google Sheets QUERY is throwing an empty data error

I need the QUERY function to (dynamically) output the contents of column C from its corresponding row. The query checks out, everything seems OK, yet the query does not output any data. I am truly puzzled by this error.

Error

Query completed with an empty output.

Formulas:

Dynamic

=QUERY(Areas, "SELECT C WHERE B = '" & B2 &"'", 0)

or

=QUERY(Areas, "SELECT C WHERE A = 'AA' AND B = '" & B2 &"'", 0)

Alternative formulas, for testing

=QUERY(Areas, "SELECT C WHERE B = 'II'", 0)

=QUERY(Areas, "SELECT C WHERE A = 'AA' AND B = 'II'", 0)

Named range

Areas = Areas!A1:C13

Data

enter image description here

Track Area Name
AA I Natural Science
AA II Social Science
AA III Humanities
AA IV Language And Rationality
AA V Global Citizenship
IGETC 1 English Communication
IGETC 2 Mathematical Concepts And Quantitative Reasoning
IGETC 3 Arts And Humanities
IGETC 4 Social And Behavioral Sciences
IGETC 5 Physical And Biological Sciences
IGETC 6 Foreign Language
IGETC 7 Ethnic Studies
Solution

  • Your formula is correct.

    The reason why you are getting the empty result is that column B has mixed data types. "When using the query() function you cannot have mixed data types in the same column. It is for this reason the query is failing." Read more here

    What you can do is change the format of the column:

    Select the whole column B> Format>Number>Custom Number Format> it the text box at the top, type '@'>Click apply

    This also works: Select the whole column B> Format>Number>Plain Text

    Sample Query

    =QUERY(Areas, "SELECT C WHERE B = '" & B2 &"'", 0)

    Sample Output

    Output
    Natural Science

    References:

    Empty Query error