QUERY(data, query, headers) runs a short query, written in a language close to SQL, over a range and spills the result. It's the most searched Sheets-only function, and a common test question.
=QUERY(A1:E21, "select A, D where B = 'Riverside' order by D desc limit 5", 1) reads: from A1:E21, take columns A and D of the rows where column B is Riverside, most expensive first, and stop after five.
A few rules cover most errors:
- The whole query is one text in double quotes. Text values inside it go in single quotes:
'Riverside'. Numbers and true/false take no quotes.
- Columns are named by their letters in the sheet, like
A and D, not by their headings.
- Clauses go in this order:
select, where, group by, pivot, order by, limit, label.
- The last argument is the number of header rows. Set it to 1 so the headings come along and aren't treated as data.
To use a cell's value, close the quotes and join it in: "where B = '"&H1&"'".