QUERY › QUERY50 / 67

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

EasyFreeQUERY

QUERY: pick columns with select

Sekar, an HR officer at a garment factory in Bandung, has to send the employee list to the attendance team. The full data includes employee IDs and base salaries that must not be shared, so she only wants to show name, division and shift.

Task

In G1, write one QUERY formula over A1:E7 (first row = headers) that shows only the Name (B), Division (C) and Shift (D) columns for every employee, including the headers.

Cells to fillG1:I7

Required functionsQUERY

Expected result

Loading the expected result…

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

Hint 1
QUERY runs an SQL-like language on your data: QUERY(data, "query text", header_rows).
Hint 2
The select clause is followed by the column letters you want, separated by commas, in capital letters.
Hint 3
The third argument is 1 because the first row holds the headers.

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

G1

=QUERY(A1:E7,"select B, C, D",1)

Explanation

=QUERY(A1:E7,"select B, C, D",1) returns columns B, C and D for every row, headers included, without copying any data. The column order follows the select list, so select D, B would put Shift first. Column letters must be capitals and refer to the sheet columns (A, B, C, …) because the data is a cell range; when the data is an array produced by a formula, use Col1, Col2 and so on.

Starting worksheet: Sheet1
Row	A	B	C	D	E
1	Employee ID	Name	Division	Shift	Base salary
2	EMP-031	Agus	Production	Morning	4,200,000
3	EMP-044	Bella	QC	Afternoon	4,600,000
4	EMP-052	Cecep	Warehouse	Night	4,100,000
5	EMP-067	Dina	Production	Afternoon	4,300,000
6	EMP-071	Erwin	Warehouse	Morning	4,150,000
7	EMP-088	Farah	QC	Morning	4,700,000
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result