ADUVIE

ADUVIE INTERNATIONAL SCHOOL | HIGH SCHOOL ICT

Dual-Board Mastery: Cambridge IGCSE 0417 & WASSCE Digital Technology

0% Complete
WEEK 06 CURRICULAR SUITE

Spreadsheet Modeling & Core Lookup Functions

Syllabus Alignment: Cambridge 0417 Unit 20; WASSCE Computer Studies (Electronic Spreadsheets)
Mission Objective: Master spreadsheet modeling, absolute vs relative referencing, lookup mechanics (VLOOKUP, HLOOKUP, XLOOKUP), and financial cell formatting for Cambridge Paper 3 examinations.

🎥 Video Lessons 💻 Practical Work ⚙️ Practice Tool 🧠 Notes 🃏 Key Words 🕵️ Common Mistakes 📝 Quiz 🎯 Self-Check

🎥 Video Lessons

Cambridge IGCSE · WAEC · Core Theory
Topic: Cambridge 0417 Paper 3: Excel Financial Modeling & VLOOKUP/HLOOKUP  |  Duration: 14:10
What to look out for: External file lookups (j2232costs to j2232currency), locking table arrays with absolute referencing ($), and enforcing FALSE exact matches.

💻 Practical Work — Exam Task & Source Files

Cambridge IGCSE 0417/32 May/June 2022 (Task 2 - Spreadsheets)
★ Required Practical Source Files (1-Click Google Classroom CDN Download):
📁 j2232costs.csv Download CDN 📁 j2232currency.csv Download CDN 📁 j2232project.csv Download CDN

Step-by-Step Task Checklist (tick each step as you complete it):

⚙️ Hands-On Practice Tool

Interactive

VLOOKUP Formula Architecture & Exact Match (`FALSE`) Inspector

Formula Engine

Examine how =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) retrieves data. Change parameters to see when #N/A or wrong approximate values occur.

=VLOOKUP("P03", $A$2:$B$6, 2, FALSE)
Formula Evaluation: Savannah Protection Project

🧠 Notes & Key Concepts

Theory Summary
🔹
• Referencing Mechanics: Relative referencing (`A1`) shifts row and column coordinates when a formula is copied/dragged. Absolute referencing (`$A$1`) locks the row and/or column coordinates, essential when referencing fixed tax rates, lookup tables, or constants.
🔹
• VLOOKUP Architecture: `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`. - `lookup_value`: The code or item being searched for. - `table_array`: The reference table (must use absolute referencing, e.g. `$A$2:$D$50`). - `col_index_num`: The column number from which to return data. - `range_lookup`: `FALSE` (or `0`) for an exact match; `TRUE` (or `1`) for an approximate match (requires table to be sorted ascending).
🔹
• HLOOKUP: Searches horizontally across rows rather than vertically down columns.
🔹
• Formatting Integrity: Exam markers penalize missing currency symbols (e.g. `$`, `€`, `₦`), incorrect decimal precision, and unformatted percentage rates.
★ Cambridge / WAEC Model Scenario:

Write a VLOOKUP formula in cell C5 to find the job title from a table located in `Staff!$A$2:$C$30`, matching the Job Code in cell B5.

💡 Worked Solution & Rationale:

`=VLOOKUP(B5, Staff!$A$2:$C$30, 2, FALSE)`
Explanation: B5 is the lookup value, `Staff!$A$2:$C$30` is the locked lookup table, 2 is the column containing Job Title, and `FALSE` enforces an exact match.

🃏 Key Words — Click a Card to Reveal the Definition

6 Terms
Card 01

Absolute Referencing

👆 Click / Tap to Reveal Definition

Definition 01

Cell reference locked with dollar signs ($A$1) that does not shift when copied across rows or columns.

Card 02

Relative Referencing

👆 Click / Tap to Reveal Definition

Definition 02

Cell reference (A1) that automatically shifts row/column coordinates when copied across rows or columns.

Card 03

VLOOKUP

👆 Click / Tap to Reveal Definition

Definition 03

Searches vertically down the first column of a table array and returns a value from the specified column index.

Card 04

Range Lookup (FALSE)

👆 Click / Tap to Reveal Definition

Definition 04

VLOOKUP argument (FALSE or 0) that strictly enforces an exact match, triggering #N/A if missing.

Card 05

HLOOKUP

👆 Click / Tap to Reveal Definition

Definition 05

Searches horizontally across the first row of a reference table array and returns a value from a specified row.

Card 06

3D Referencing

👆 Click / Tap to Reveal Definition

Definition 06

Formulas that reference identical cells or ranges across multiple distinct worksheet tabs.

🕵️ Common Exam Mistakes — Can You Spot the Error?

Examiner Trap
⚠️ Candidate Error Trap:

A student wrote `=VLOOKUP(A2, J1:K10, 2)` and dragged the formula down 100 rows. The first 3 rows worked, but lower rows returned `#N/A` errors.

🎯 Your Detective Mission:

Explain why the lower rows returned `#N/A` errors and write the corrected formula.

📝 Quiz — Test Yourself (10 Questions)

Instant Marking & Explanation
1. Which formula correctly locks both the row and column of cell B4?
A) `$B4`
B) `B$4`
C) `$B$4`
D) `$$B$$4`
Mark Scheme Rationale: `$B$4` locks both column B and row 4.
2. What does the `FALSE` parameter represent in a VLOOKUP function?
A) Return the formula text
B) Enforce an exact match
C) Use approximate match
D) Ignore errors
Mark Scheme Rationale: `FALSE` (or `0`) demands an exact match.
3. What error is returned when VLOOKUP cannot find the lookup value?
A) `#VALUE!`
B) `#REF!`
C) `#N/A`
D) `#DIV/0!`
Mark Scheme Rationale: `#N/A` indicates 'Not Available' (lookup item not found).
4. Which function searches horizontally across the top row of a table array?
A) `VLOOKUP`
B) `HLOOKUP`
C) `LOOKUP`
D) `INDEX`
Mark Scheme Rationale: `HLOOKUP` searches horizontal rows.
5. What must be true about the first column of a lookup table when using approximate match (`TRUE`)?
A) It must be sorted in ascending order
B) It must be text only
C) It must be sorted descending
D) It must contain no numbers
Mark Scheme Rationale: Approximate matching requires ascending order sort.
6. Which keyboard shortcut toggles absolute referencing ($) on a selected cell reference in Excel?
A) F2
B) F4
C) F5
D) F9
Mark Scheme Rationale: F4 toggles absolute and relative referencing.
7. What error occurs if the column index number in VLOOKUP exceeds the total columns in the table array?
A) `#N/A`
B) `#REF!`
C) `#NAME?`
D) `#NULL!`
Mark Scheme Rationale: `#REF!` indicates an invalid column reference.
8. Which function is the modern replacement for VLOOKUP that can search in any direction without column indexing?
A) `SEARCH`
B) `XLOOKUP`
C) `FIND`
D) `MATCH`
Mark Scheme Rationale: `XLOOKUP` can search in any direction.
9. What formula calculates the total sum of cells B2 through B20?
A) `=ADD(B2:B20)`
B) `=TOTAL(B2:B20)`
C) `=SUM(B2:B20)`
D) `=COUNT(B2:B20)`
Mark Scheme Rationale: `=SUM(B2:B20)` is the correct summation function.
10. How should a percentage rate of 7.5% be entered into a calculation formula?
A) `7.5`
B) `0.075` (or `7.5%`)
C) `75%`
D) `0.75`
Mark Scheme Rationale: 7.5% is mathematically 0.075.

Your Score: 0 / 10 (0%)

Answer the questions above to see your score.

🎯 How Did You Find This Week?

Self-Check

1. Rate your understanding of Week 06 (Spreadsheet Modeling & Core Lookup Functions):

🏆 Got it — Exam Ready
⚖️ Mostly — Needs More Practice
🚨 Not Yet — Need Help

2. Next step: Open your YEAR 12 ICT & DIGITAL TECHNOLOGY — LIVING EXAM TRACKER.xlsx on Google Classroom and update your score for this week.