QUERY function with one result returns value beneath the formula cell

Question

When I use the =QUERY function in Google Sheets, it returns the value in the cell beneath where I typed the function. I want the returned value to appear in the cell I typed the function. Currently, I have

=QUERY(Data!$L:$N,"select avg(N) where L = '"&CONCATENATE(B$1,$A2)&"'")

in cell B2, and it is returning the value in B3.

How can I return the value into B2 instead of B3?

Answer 1

It is returning you an array containing the name of the calculating SQL function first and then the result. To subvert this behavior, you could do the following.

=INDEX(QUERY(Data!$L:$N,"select avg(N) where L = '"&CONCATENATE(B$1,$A2)&"'"),2)

Answer 2

The query language itself allows for this by permitting application of a blank label:

=QUERY(Data!$L:$N,"select avg(N) where L='"&B$1&$A2&"' label avg(N)''")