Lookup and reference functions
Lesson details
Learning outcome
I can use lookup and IF functions to retrieve data efficiently.
Key learning points
- The VLOOKUP function is used to retrieve data from another sheet based on a matching value.
- Lookup and reference functions can be useful for working with sets of data efficiently.
- Data can be stored in a database, which is an organised collection of data.
- The IF function checks a condition and returns different outcomes depending on whether the condition is True or False.
- The IF function is used to give user feedback in a spreadsheet.
Keywords
Database - an organised collection of data
Function - a predefined formula that performs a calculation
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.
Teacher tip
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.
Equipment
Pupils will need access to a device with spreadsheet software. Examples in this lesson use Google Sheets https://oak.link/google-sheets
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.
Licence
Lesson video
Loading...
Prior knowledge starter quiz
6 Questions
Q1.What is the purpose of conditional formatting in spreadsheets?
Q2.How does data validation help prevent mistakes?
Q3.What are criteria in data validation?
Q4.Arrange the steps to set up data validation in the correct order:
Q5.How can data validation improve consistency in spreadsheets?
Q6.Match the terms with their correct descriptions:
a tool that controls what kind of data can be entered into a cell
the rules that decide what data is allowed
a warning shown when data doesn’t meet the criteria
keeping data accurate and uniform
Assessment exit quiz
6 Questions
Q1.What is a database?
Q2.What is the purpose of the VLOOKUP function?
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.
Q4.What does the IF function do in a spreadsheet?
Q5.Which of the following statements about the IF function is incorrect?
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.
To help you plan your 11 computing lesson on: Lookup and reference functions, download all teaching resources for free and adapt to suit your pupils' needs...
To help you plan your 11 computing lesson on: Lookup and reference functions, download all teaching resources for free and adapt to suit your pupils' needs.
The starter quiz will activate and check your pupils' prior knowledge, with versions available both with and without answers in PDF format.
We use learning cycles to break down learning into key concepts or ideas linked to the learning outcome. Each learning cycle features explanations with checks for understanding and practice tasks with feedback. All of this is found in our slide decks, ready for you to download and edit. The practice tasks are also available as printable worksheets and some lessons have additional materials with extra material you might need for teaching the lesson.
The assessment exit quiz will test your pupils' understanding of the key learning points.
Our video is a tool for planning, showing how other teachers might teach the lesson, offering helpful tips, modelled explanations and inspiration for your own delivery in the classroom. Plus, you can set it as homework or revision for pupils and keep their learning on track by sharing an online pupil version of this lesson.
Explore more key stage 4 computing lessons from the Spreadsheets unit, dive into the full secondary computing curriculum, or learn more about lesson planning.