Dynamic arrays › CHOOSECOLS, FILTER107 / 144

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

MediumFreeCHOOSECOLSFILTER

CHOOSECOLS + FILTER: Marketing team contacts

Nadia from HR has been asked to set up a WhatsApp group for the Marketing team. The employee table includes ID numbers and salaries that must not be shared, so she only needs the names and phone numbers of the Marketing staff.

Task

In H2, write one formula that filters A2:F10 for the Marketing division with FILTER, then keeps only the Name (2nd) and Phone (6th) columns with CHOOSECOLS. The result spills into H2:I5.

Cells to fillH2:I5

Required functionsCHOOSECOLSFILTER

Expected result

Loading the expected result…

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

Hint 1
CHOOSECOLS(array, col_num1, col_num2, …) picks specific columns from an array. Column numbers count from the left of the array.
Hint 2
Filter the rows first: FILTER(A2:F10, C2:C10="Marketing"). That result becomes the array for CHOOSECOLS.
Hint 3
Pattern: =CHOOSECOLS(FILTER(…), 2, …).

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

H2

=CHOOSECOLS(FILTER(A2:F10,C2:C10="Marketing"),2,6)

Explanation

=CHOOSECOLS(FILTER(A2:F10,C2:C10="Marketing"),2,6) filters the 4 Marketing employees, then keeps only the 2nd and 6th columns. Column order is up to you: CHOOSECOLS(…,6,2) puts the phone number first. Unlike copying columns by hand, this formula picks up new Marketing hires automatically. To pick rows by position, use its sibling, CHOOSEROWS.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F	H	I
1	NIK	Name	Division	City	Salary	Phone	Name	Phone
2	K-1021	Rizky Pratama	Finance	Jakarta	9,200,000	0812-1100-2231
3	K-1022	Melati Putri	Marketing	Jakarta	8,500,000	0813-2201-4410
4	K-1023	Bayu Saputra	IT	Bandung	11,000,000	0857-3300-1298
5	K-1024	Nabila Zahra	Marketing	Bandung	7,800,000	0821-4455-0912
6	K-1025	Hendra Wijaya	HRD	Jakarta	8,900,000	0811-7788-3021
7	K-1026	Sinta Maharani	Marketing	Surabaya	8,100,000	0878-5512-6630
8	K-1027	Dodi Kurniawan	IT	Jakarta	12,500,000	0812-9081-7734
9	K-1028	Lia Anggraini	Finance	Surabaya	7,600,000	0838-6620-1145
10	K-1029	Yoga Firmansyah	Marketing	Jakarta	9,400,000	0815-2209-8876
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result