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?

Because the word AVG sits in B2? The documentation suggests that is normal. It has a similar example with MAX. — Jeremy Kahan, Jun 10 '18 at 04:10

score 13 · Answer 1 · answered Jun 10 '18 at 04:16

13

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)

answered Jun 10 '18 at 04:16

Jeremy Kahan

3,796
1
10
23

2

you can also use something like this `=QUERY(Data!$L:$N,"select avg(N) where L = '"&CONCATENATE(B$1,$A2)&"' limit 1")` if you just want one result only instead of an array – wildmonkey Sep 19 '20 at 12:10

score 6 · Answer 2 · answered Aug 03 '18 at 18:21

6

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)''")

answered Aug 03 '18 at 18:21

pnuts

58,317
11
87
139

way more direct for this case of running a scalar query – Mike M Aug 18 '23 at 15:31

QUERY function with one result returns value beneath the formula cell

2 Answers2