QUERY › QUERY54 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeQUERY

QUERY: where … contains

Uda Rafi's coffee shop in Padang wants to review sales of every menu item whose name contains the word "Kopi" (Indonesian for coffee), wherever the word appears.

Task

In E1, write one QUERY formula over A1:C9 that shows the Menu item (A) and Sold (B) columns for items whose name contains the text Kopi, including the headers.

Cells to fillE1:F5

Required functionsQUERY

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
The contains operator checks whether a text includes a given piece anywhere.
Hint 2
The text to match goes in single quotes: A contains 'Kopi'.
Hint 3
The columns to show: select A, B, then add the condition after where.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E1

=QUERY(A1:C9,"select A, B where A contains 'Kopi'",1)

Explanation

contains finds "Es Kopi Susu", "Kopi Tubruk", "Kopi Aren Latte" and "Affogato Kopi" because the word can be at the start, middle or end. The operator is case-sensitive: contains 'kopi' would find nothing in this data. If the data is typed inconsistently, use lower(A) contains 'kopi'. For freer patterns, QUERY also has like (with % as a wildcard) and matches (regex).

Starting worksheet: Sheet1
Row	A	B	C
1	Menu	Sold	Price
2	Es Kopi Susu	320	18,000
3	Kopi Tubruk	140	10,000
4	Teh Tarik	95	15,000
5	Kopi Aren Latte	210	22,000
6	Roti Bakar Cokelat	80	20,000
7	Es Teh Lemon	120	12,000
8	Affogato Kopi	45	28,000
9	Susu Jahe	60	14,000
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result