Return a specific cell in a Google Sheets QUERY function

Question

Take the following spreadsheet:

I want to create a query that returns the values in columns A and B where A matches 'f' and also return the value that is in cell D1 (Test Cell).

Here is the Query that I wrote:

=QUERY(A2:B7,"select A,B,'"&D1&"' where A='f'")

This is what I expected:

This is what actually happens:

I'm not sure where the value of "Test Cell()" in C11 is coming from and I suspect it's a bug. Is there a way to accomplish what I am attempting?

what you are doing here, is you created a column with plain text from cell D1, QUERY will add LABEL to any created column by it self as header, if you don't want it, add this to your query ```label '"&D1&"' ''```, this will tell the query to replace the header of that newly created column with ```''``` (nothing) — Ping, Nov 24 '22 at 03:49

score 2 · Accepted Answer · answered Nov 24 '22 at 03:49

2

try:

=QUERY(A2:B7, "select A,B,'"&D1&"' where A='f' label '"&D1&"'''")

or:

=QUERY(QUERY(A2:B7, "select A,B,'"&D1&"' where A='f'", ), "offset 1", )

answered Nov 24 '22 at 03:49

player0

1 Answers1