Skip to main content
SingaporeComputer ScienceSyllabus dot point

How can a spreadsheet make decisions and look up values from a table?

Use the IF function for conditional results and lookup functions such as VLOOKUP to find values in a table

A focused answer to the O-Level Computing point on logical and lookup functions. Using IF for conditional results, nesting IF for grades, and VLOOKUP to find a matching value in a table.

Generated by Claude Opus 4.87 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Examples in context
  4. Try this

What this dot point is asking

SEAB wants you to use the IF function to produce a result that depends on a condition, and to use a lookup function such as VLOOKUP to find a value in a table. The central idea is that IF lets a spreadsheet make a decision, while a lookup function lets it find a matching entry automatically rather than searching by eye.

The answer

The IF function

The IF function chooses between two results based on a condition. It takes three parts:

=IF(condition, value_if_true, value_if_false)

The condition is usually a comparison such as B2>=50. If it is true, the function returns the first value; if false, it returns the second.

=IF(B2>=50, "Pass", "Fail")

Text results are written in quotation marks; numbers are not.

Comparison operators

IF conditions use comparison operators: = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and <> (not equal to).

Nesting IF for more than two outcomes

For three or more outcomes, put another IF in the false part. The conditions are checked from the outside in:

=IF(B2>=70, "Distinction", IF(B2>=50, "Pass", "Fail"))

Order matters: check the highest band first, so a mark of 8585 is caught by >=70 and never reaches the lower tests.

The VLOOKUP function

VLOOKUP (vertical lookup) finds a value in the first column of a table and returns a value from another column in the same row. It takes four parts:

=VLOOKUP(value, table, column_number, FALSE)
  • value: what to search for.
  • table: the range holding the data.
  • column_number: which column of the table to return (counting from 11 on the left).
  • FALSE: asks for an exact match.
=VLOOKUP(D2, A2:B5, 2, FALSE)

VLOOKUP always searches the leftmost column of the table, so the value you look up must sit in that first column.

Examples in context

Example 1. Awarding grades. A teacher uses a nested IF on each student's mark to display A, B, C or F automatically. When marks are entered, the grade column fills itself, and a change to a mark updates the grade instantly.

Example 2. A price list at a counter. A cafe stores item prices in a lookup table and uses VLOOKUP so that typing an item code shows its price. Staff never search the list by eye, and updating a price in the table updates every sale that looks it up.

Try this

Q1. Write an IF function in B2 that shows "Yes" if A2 is greater than 100100, otherwise "No". [2 marks]

  • Cue. =IF(A2>100, "Yes", "No").

Q2. In =VLOOKUP(D2, A2:C10, 3, FALSE), state which column's value is returned. [2 marks]

  • Cue. The third column of the table range (column C), the column counted as 33 from the left.

Q3. Explain why the search value for VLOOKUP must be in the first column of the table. [2 marks]

  • Cue. VLOOKUP only searches the leftmost column for the value, then counts columns to the right to return the result.

Exam-style practice questions

Practice questions written in the style of SEAB exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

Original5 marksA mark is in cell B2. (a) Write an IF function in C2 that displays 'Pass' if the mark is 5050 or more, and 'Fail' otherwise. (b) Extend the idea to show 'Distinction' for 7070 or more, 'Pass' for 5050 to 6969, and 'Fail' below 5050, by nesting IF functions.
Show worked answer →

(a) The IF function takes a condition, a value if true, and a value if false:

=IF(B2>=50, "Pass", "Fail")

(b) Nest a second IF inside the false part to handle three bands:

=IF(B2>=70, "Distinction", IF(B2>=50, "Pass", "Fail"))

The outer IF checks 7070 first; if false, the inner IF checks 5050; if that is also false, the result is "Fail".

Markers reward the correct IF structure (condition, true value, false value), the boundary ≥50\ge 50, and a correctly nested IF for the three bands.

Original5 marksA table in cells A2:B5 lists product codes in column A and prices in column B. A code is entered in cell D2. (a) Write a VLOOKUP function in E2 to find the matching price. (b) Explain why the lookup column must be the leftmost column of the table.
Show worked answer →

(a) VLOOKUP searches the first column of a table for the value, then returns a value from another column in the same row:

=VLOOKUP(D2, A2:B5, 2, FALSE)

D2 is the value to find, A2:B5 is the table, 22 is the column to return (the price), and FALSE asks for an exact match.

(b) VLOOKUP always searches the leftmost column of the table range for the lookup value. So the product code, which is what we search for, must be in the first column; the price we want to return is then found by counting columns to the right.

Markers reward the VLOOKUP arguments (value, table, column number, exact match) and the reason that VLOOKUP only searches the first column.

Related dot points