Arrays & ARRAYFORMULA › FILTER45 / 67

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

MediumFreeFILTER

FILTER: two conditions, Google Sheets style

The HR team of a logistics startup is hiring a warehouse admin. Yuni wants a list of applicants from Surabaya whose logic test score is at least 75, to invite them to an interview.

Task

In F2, write one FILTER formula over A2:D10 that returns applicants whose city is Surabaya and whose test score is at least 75. Write the two conditions as separate arguments. The result spills into F2:I5.

Cells to fillF2:I5

Required functionsFILTER

Expected result

Loading the expected result…

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

Hint 1
In Google Sheets, FILTER(range, condition1, condition2, …) takes several conditions at once; a row is kept only if all of them are true.
Hint 2
Each condition is a one-column range as tall as the data, for example B2:B10="Surabaya".
Hint 3
The second condition uses the test score column and the >= operator.

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

F2

=FILTER(A2:D10,B2:B10="Surabaya",C2:C10>=75)

Explanation

=FILTER(A2:D10,B2:B10="Surabaya",C2:C10>=75) keeps only the rows that meet both conditions: Andi, Dewi, Fitri and Indra. Dewi is included because the condition is >= 75, not > 75. In Google Sheets you write FILTER in one cell and the result spills down. For OR logic, add conditions with +, for example (B2:B10="Surabaya")+(B2:B10="Gresik"). If no row matches, the result is #N/A, so wrap it in IFERROR when needed.

Starting worksheet: Sheet1
Row	A	B	C	D	F	G	H	I
1	Name	City	Test score	Experience (yrs)	Name	City	Test score	Experience (yrs)
2	Andi	Surabaya	82	2
3	Bella	Sidoarjo	90	1
4	Cahyo	Surabaya	70	4
5	Dewi	Surabaya	75	0
6	Eko	Malang	88	3
7	Fitri	Surabaya	93	5
8	Galih	Gresik	79	2
9	Hana	Surabaya	64	1
10	Indra	Surabaya	77	3
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result