Cambridge Notes, Past Papers, Revision Questions

Cambridge IGCSE ICT 0417 – Topic 20: Spreadsheets – Searching with Operators

Topic 20 – Spreadsheets

Objective

Be able to perform searches using a variety of operators including AND, OR, NOT, >, <, =, >=, <=, <> in spreadsheet formulas.

1. Why Use Search Operators?

Search operators allow you to locate, filter, or evaluate data based on specific criteria. They are essential for:

Finding records that meet multiple conditions.

Creating dynamic reports and dashboards.

Automating decision‑making processes.

2. Logical Operators

Logical operators combine or modify conditions.

Operator	Meaning	Example (Excel/Google Sheets)	Result
AND	True only if all conditions are true	=AND(A2>50, B2="Yes")	TRUE if A2 is greater than 50 and B2 equals “Yes”.
OR	True if any condition is true	=OR(A2<20, C2="Pending")	TRUE if A2 is less than 20 or C2 equals “Pending”.
NOT	Inverts a logical value	=NOT(D2="Complete")	TRUE if D2 does not equal “Complete”.

3. Comparison Operators

Comparison operators test the relationship between two values.

Operator	Meaning	Example	Result
>	Greater than	=A2>100	TRUE if the value in A2 is larger than 100.
<	Less than	=B2<=50	TRUE if B2 is 50 or less.
=	Equal to	=C2="Apple"	TRUE if C2 contains the text “Apple”.
>=	Greater than or equal to	=D2>=75	TRUE if D2 is at least 75.
<=	Less than or equal to	=E2<=30	TRUE if E2 is 30 or lower.
<>	Not equal to	=F2<>"N/A"	TRUE if F2 does not contain “N/A”.

4. Combining Logical and Comparison Operators

Complex searches often require a mix of logical and comparison operators. Use parentheses to control the order of evaluation.

Example: Find rows where the sales amount is greater than 500 and the region is either “North” or “East”.

=AND(A2>500, OR(B2="North", B2="East"))

Explanation:

A2>500 checks the numeric condition.

OR(B2="North", B2="East") checks the text condition.

AND(...) ensures both groups are true.

5. Using SEARCH Functions with Operators

Most spreadsheet programs provide functions that incorporate these operators directly:

COUNTIF / COUNTIFS – counts cells that meet one or multiple criteria.

SUMIF / SUMIFS – adds values that meet criteria.

FILTER (Google Sheets) – returns an array of rows that satisfy conditions.

VLOOKUP / HLOOKUP / XLOOKUP – locate a value based on a key; the key can be defined with operators.

Example: COUNTIFS with Multiple Operators

=COUNTIFS(A2:A100, ">=200", B2:B100, "<>""", C2:C100, "Yes")

This counts rows where:

Column A is at least 200.

Column B is not blank.

Column C equals “Yes”.

6. Practical Exercise

Create a spreadsheet with the following columns: Student ID, Score, Passed (Yes/No), Region.

Enter at least 15 rows of sample data.

In a new column, use a formula to flag students who scored >=70 and are from “South”.

Use COUNTIFS to determine how many students meet the above condition.

Apply a FILTER (or Advanced Filter in Excel) to display only those flagged rows.

7. Common Mistakes to Avoid

For text comparisons, enclose the text in double quotes (“”).

Do not forget to use the correct operator for “not equal to” – it is <>, not != (unless using a scripting language).

When combining operators, always use parentheses to avoid unintended precedence.

Remember that comparison operators work with numbers and dates; for text you usually need logical functions like SEARCH or FIND.

8. Summary Table

Operator	Symbol in Formula	Typical Use
AND	AND(condition1, condition2, …)	All conditions must be true.
OR	OR(condition1, condition2, …)	At least one condition true.
NOT	NOT(condition)	Invert a condition.
Greater than	>	Numeric or date comparison.
Less than	<	Numeric or date comparison.
Equal to	=	Exact match (numbers, dates, or text).
Greater than or equal	>=	Inclusive upper bound.
Less than or equal	<=	Inclusive lower bound.
Not equal to	<>	Exclude a specific value.

Suggested diagram: Flowchart showing how AND, OR, and NOT combine with comparison operators to produce a final logical result.

Be able to perform searches using a variety of operators including AND, OR, NOT, >, <, =, >=, <=, <>