GCSE ICT – help with planning

Unit 2 Skills Builder Block 2 Lesson plans

SB 2 / Lesson 2 – Using Function VLOOKUP / Time /
Lesson objectives / In this lesson students are learning about
·  the Function LOOKUP
·  differences between VLOOKUP and HLOOKUP
Learning outcomes / At the end of this lesson students will be able to:
·  use the Function VLOOKUP in a spreadsheet
Specification coverage / 2.1 Adapt and enhance spreadsheet models
Resources
SB2 L2 VLOOKUP.xls
SB2 L2 Challenge2.xls
SB2 L2 VLOOKUP HLOOKUP.gif
SB2 L2 VLOOKUP.gif
Key vocabulary
Function
HLOOKUP
Horizontal
Vertical
VLOOKUP / Starter
Open SB2 L2 VLOOKUP.xls and show sheet ‘TableV Arrays match-locate’.
Recap with students the three steps they need to take to look up information in a table.
Minimise SB2 L2 VLOOKUP.xls and open SB2 L2 Challenge2.xls. Show students sheet ‘Horizontal Table’, and ask them how they got on with the challenge you set as homework.
Move to sheet ‘Horizontal Arrays’. It shows one of the arrays (blue) used in the last lesson and, in green, the array for the homework. Mention that columns H to J are hidden so that the spreadsheet fits better onto the screen / board. / 10 mins
With the sheet ‘Horizontal Arrays’ on the board, ask students similar questions to those you asked in Lesson 1, but tell them to give you both the information and its location.
Array H1 (blue)
·  For ‘Item3’, what is the ‘Discount’? = 3%, blue Row 5
·  For ‘Item12’, what is the ‘Source’? = www.link12, blue Row 9
Ask students, as in the previous lesson, to describe where they started from, how they moved within the table, and where they ended up. The way of travel this time is always:
1.  right across the first row to find the match, then
2.  then down to locate the information wanted.
Minimise SB2 L2 Challenge2.xls.
Conclude the Starter by showing students SB2 L2 VLOOKUP HLOOKUP.gif.
Stress both models produce correct results but in different ways. Tell students they need to remember the two models when they design their own tables. In this lesson they will work with VLOOKUP.
Share lesson objectives
Introduce the lesson and share lesson objectives and learning outcomes. / 5 mins
VLOOKUP – a demonstration
Maximise SB2 L2 VLOOKUP.xls and show students sheet ‘VLOOKUP + 2 User Inputs’. Explain that this sheet is linked to sheet ‘Table Arrays’ and colour coded in the same way.
Give students access to ‘VLOOKUP + 2 User Inputs’ on the spreadsheet and allow them 10 minutes to
·  follow the instructions in cells B4 and B9
·  work out how the function works.
Collect feedback from students, in particular about the input of ‘5.2’ into C9. Remind students that you asked them a question about ‘5.2’ last lesson but left the answer hanging until today.
Tease out how TRUE and FALSE work with text only, text and numbers, numbers only. What is taken into account by the function, and what is not (e.g. capital letters).
As a demonstration, input ‘Item12’ into cell C4. Both TRUE and FALSE give a response but they are not the same. Ask the students to find an explanation.
Cover any gaps by taking students through one example:
Click on cell G6 and point out:
=VLOOKUP is the start of the function
$ - absolute cell references, for the array: allows students to copy and paste formula without loosing the array
$C$4 is an absolute reference to the cell in which the user input information
‘Table Arrays’ is the name of the sheet is the array is on
$C$6:$K$17 is the range of the array
4 is the number of the array column in which the wanted information is
TRUE / FALSE is for an exact or approximate match. / 15 mins
VLOOKUP with drop-down input lists
Refer students back to their experience of inputting data into cells C4 and C9. Ask them how they can reduce or eliminate the risk of ‘user input error’, for example by making spelling mistakes. What data would they ideally like users to input? Is there a way that they can limit user inputs and, at the same time, make inputs user-friendly (no repetitive or long typing)?
Show sheet ‘VLOOKUP + 2 Drop-Lists’ on the board. Click in cell C4. Let students read the prompt, then click on the down-arrow.
When the list appears, scroll up and down, then ask the students where the items on the list come from – answer: cells C6:C17 on sheet ‘Table Arrays’.
Refer students to the instructions in cells B4 and B9. Before you attempt to enter any data, ask students another ‘What if …’ question: What will happen if I enter data not on the list?
Collect several answers, then click on cell C4 and enter ‘Item 5’ or ‘Item55’ – but do not press Enter yet.
Point out that users can enter data that is not on the list – now press Enter and invite students to comment on the outcome. Ask how the response “Input not on list” could be improved.
Use one of the students’ suggestions and show them where on the menu the drop-down list is (Data > Validation). Explain why validation is important, and the importance of sensible error messages:
With cell C4 highlighted, click on ‘Data’, then ‘Validation’ – the details for List in Cell A4 will be displayed.
Click on ‘Error Alert’ and amend the ‘Error message’.
Click on ‘Input Message’ and make a change.
Click on ‘Settings’ and focus on ‘Source’ / 10 mins
Note: the way in which the ‘Source’ for a list is created and displayed depends on the spreadsheet software the students use.
For example, in MS Excel 2003, users first highlight the range of cells they want to use for a list, then go to ‘Insert’, ‘Name’, ‘Define’ and give the range a name. Users then enter that name (proceeded by =) when they create the drop-down list (‘Data’, ‘Validation’).
In MS Excel 2010, users do not have to ‘name’ a range of cells first. They can select their range of cells when they create the list (‘Data’, ‘Data Validation’).
VLOOKUP – student worked example
Display the formula in cell G6 again. Cells G10 and G11 display similar information as Cell G6. Check students understanding by asking them to predict the formula in G10.
Click on cell G10 to verify predictions. If students are uncertain, leave G10 on the screen and ask students to predict the formula in cell J6.
Conclude with a demonstration of ‘Insert function’ or the function wizard in the students’ spreadsheet software. Ask students to complete Example 3 on sheet ‘VLOOKUP + 2 User Inputs’. Students can use a different colour to highlight the array they need.
Tell students to remember to format cells so that their content is easy to read and understand. Warn them that LOOKUP’s main function is to get data, not formatting features. The different colours shown in the table for Colour1 – Colour12 will be difficult if not impossible to replicate. Students need to be aware of LOOKUP’s limitations when creating their own data table.
If students need some reminders, display SB2 L2 VLOOKUP.gif. / 15 mins
Plenary
Take students back to the beginning of the lesson. Maximise SB2 L2 Challenge2.xls and show them sheet ‘Horizontal match-locate’.
Ask students for some inputs to test the LOOKUP Function. Leave the cursor on cell F18 to display the Function HLOOKUP.
Sum up that the LOOKUP Function helps to save time and effort, and to avoid errors.
It supports the good practice of ‘enter once, use many times’, and equally ‘check once, use many times’.
Conclude the lesson by introducing the homework, another challenge. / 5 mins
Homework / To create a drop-down input list in cell C15 for their Example 3 in the sheet ‘VLOOKUP + 2 Drop-Lists’ in SB2 L2 VLOOKUP.xls.
Practice using the VLOOKUP Function. / 20 mins
Notes / Before Lesson 3, play through SB2 L3 What if – outside light.ppt. Slide 2 contains animations - please check and adjust them if necessary.

SB2 Lesson Plan 2