Skip to content
Oak National Academy
Oak National Academy
View my library
0
Teachers
Pupils
New
New
Year 11

Lookup and reference functions

I can use lookup and IF functions to retrieve data efficiently.

Download all resources
Share activities with pupils
New
New
Year 11

Lookup and reference functions

I can use lookup and IF functions to retrieve data efficiently.

Download all resources
Share activities with pupils

These resources will be removed by end of Summer Term 2025.

Switch to our new teaching resources now - designed by teachers and leading subject experts, and tested in classrooms.

These resources were created for remote use during the pandemic and are not designed for classroom teaching.

View new resources

Lesson details

Key learning points

  1. The VLOOKUP function is used to retrieve data from another sheet based on a matching value.
  2. Lookup and reference functions can be useful for working with sets of data efficiently.
  3. Data can be stored in a database, which is an organised collection of data.
  4. The IF function checks a condition and returns different outcomes depending on whether the condition is True or False.
  5. The IF function is used to give user feedback in a spreadsheet.

Keywords

  • Database - an organised collection of data

  • Function - a built-in formula in a spreadsheet that helps you do calculations quickly

  • LOOKUP - a function that looks for a value in one column and finds matching information from the same row in another column

  • IF - a function in a spreadsheet that checks if something is True or False

Common misconception

The IF function can only return the words True or False to the user.

The IF function returns a result of True or False, but you can set what message will be displayed to the user for each result within the function.


To help you plan your year 11 computing lesson on: Lookup and reference functions, download all teaching resources for free and adapt to suit your pupils' needs...

Files needed for this lesson

  • Rockstar merchandise solution 100.21 KB (XLSX)
  • Rockstar merchandise 99.24 KB (XLSX)

Download these files to use in the lesson.

Download lesson files
This lesson contains downloadable spreadsheet files that pupils will need for the lesson. You could download these files prior to the lesson and make available to pupils on your school's shared drive or cloud storage area.
Teacher tip

Equipment

Pupils will need access to spreadsheet software.

Licence

This content is © Oak National Academy Limited (2025), licensed on Open Government Licence version 3.0 except where otherwise stated. See Oak's terms & conditions (Collection 2).

Lesson video

Skip lesson video

Loading...

Prior knowledge starter quiz

Download quiz pdf

6 Questions

Q1.
What is the purpose of conditional formatting in spreadsheets?
to automatically delete incorrect data
Correct answer: to apply formatting based on specific rules
to create charts from data
to protect cells from being edited
Q2.
How does data validation help prevent mistakes?
by automatically correcting errors
by deleting incorrect data
by formatting data automatically
Correct answer: by limiting the type of data that can be entered
Q3.
What are criteria in data validation?
Correct answer: the rules that decide what data is allowed
the design of the spreadsheet
the layout of the cells
the default settings for data entry
Q4.
Arrange the steps to set up data validation in the correct order:
1 - Select the cell or range of cells.
2 - Choose the data validation option.
3 - Set the criteria.
4 - Save the settings.
Q5.
How can data validation improve consistency in spreadsheets?
by formatting all cells the same way
by creating automatic charts
by deleting duplicate data
Correct answer: by ensuring only allowed data is entered
Q6.
Match the terms with their correct descriptions:
Correct Answer:data validation,a tool that controls what kind of data can be entered into a cell

a tool that controls what kind of data can be entered into a cell

Correct Answer:criteria,the rules that decide what data is allowed

the rules that decide what data is allowed

Correct Answer:error message,a warning shown when data doesn’t meet the criteria

a warning shown when data doesn’t meet the criteria

Correct Answer:consistency,keeping data accurate and uniform

keeping data accurate and uniform

Assessment exit quiz

Download quiz pdf

6 Questions

Q1.
What is a database?
a collection of unorganised data
a built-in spreadsheet function
Correct answer: an organised collection of data
a chart created in a spreadsheet
Q2.
What is the purpose of the VLOOKUP function?
to calculate the sum of a column
Correct answer: to retrieve data from another sheet based on a matching value
to filter data within a sheet
to create a chart
Q3.
Write the formula to retrieve data from the second column of the range T1:W12, from a sheet called LOOKUP sheet, matching the value in cell A1.
Correct Answer: =VLOOKUP(A1,T1:W12,2,FALSE)
Q4.
What does the IF function do in a spreadsheet?
Correct answer: checks a condition and returns outcomes based on whether it is True or False
filters data based on criteria
looks up values in a database
creates a chart
Q5.
Which of the following statements about the IF function is incorrect?
The IF function can check a condition and return different outcomes.
The IF function can display custom messages for True and False results.
Correct answer: The IF function can only return the words "True" or "False".
The IF function can be used to provide user feedback.
Q6.
Write the formula that checks if the value in cell A1 is less than 50 and returns "Low" if True or "High" if False.
Correct Answer: =IF(A1<50,"Low","High")