Spreadsheet Practice

How to use QUERY as a lookup in Google Sheets

Google Sheets formula

=QUERY(A2:D9,"select B, D where A = '"&G1&"'",0)

Put the lookup value into the query's where clause: =QUERY(A2:D9,"select B, D where A = '"&G1&"'",0) returns the chosen columns for the matching row.

Result on the example
a 1-row list that spills from G2 in G2
Excel
See below

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

How it works

QUERY runs a small SQL-like statement on the range. select B, D picks the product and stock columns and where A = 'P-104' keeps the matching row. The &G1& joins the cell into the text, wrapped in single quotes because it is text. The final 0 says the range has no header row.

=QUERY(A2:D9,"select B, D where A = '"&G1&"'",0)

Result{1 row × 2 cols} "Standing desk", 18

  1. A2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …

    A range: 8 rows × 4 columns.

  2. G1"P-104"

    The value in G1.

  3. "select B, D where A = '"&G1"select B, D where A = 'P-104"

    The & operator joins as text.

  4. "select B, D where A = '"&G1&"'""select B, D where A = 'P-104'"

    The & operator joins as text.

  5. QUERY(A2:D9,"select B, D where A = '"&G1&"'",0){1 row × 2 cols} "Standing desk", 18

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …
    • query"select B, D where A = '"&G1&"'""select B, D where A = 'P-104'"
    • headers0

In Excel

Excel has no QUERY function. Return the same columns with =CHOOSECOLS(FILTER(A2:D9,A2:A9=G1),2,4), or with XLOOKUP and a multi-column return range.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=CHOOSECOLS(FILTER(A2:D9,A2:A9=G1),2,4)spills 1 × 2The FILTER version: no query text to build, and it works in Excel too.

Common mistakes

  • Text values need single quotes inside the query: where A = '"&G1&"'. Numbers don't: where C > "&G2.
  • Column letters refer to the sheet columns of the range. If the range starts at column B, the first column is B, not A.
  • QUERY comparisons are case-sensitive: 'p-104' won't find P-104. Use lower(A) = '"&LOWER(G1)&"' to ignore case.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use QUERY as a lookup

In G2, use QUERY to return the product name and stock for the SKU in G1.

Questions

How do I look up with two conditions?

Join them with and: "select B where A = '"&G1&"' and D > 20".

How do I match part of the text?

Use contains: "select A, B where B contains 'desk'".

How do I keep the header row in the result?

Include row 1 in the range and set the last argument to 1: =QUERY(A1:D9,"select B, D where A = '"&G1&"'",1).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.