# How to use QUERY as a lookup in Google Sheets

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.

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

**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.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-104 |
| P-101 | Desk lamp | 39.99 | 140 |  | Product and stock |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

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

Result (spills 1 × 2): Standing desk, 18

## 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.

## Other ways

- `=CHOOSECOLS(FILTER(A2:D9,A2:A9=G1),2,4)`: The 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/query-lookup-google-sheets