Why might a spreadsheet return a label for a score of 78 even though the lookup table contains no row labelled 78? An approximate lookup can use a sorted table of thresholds and select the greatest qualifying threshold at or below a value. That is a different question from an exact lookup, which requires a matching identifier. Confusing the two can produce a plausible-looking but incorrect result.
Ministry of Education V3.0 Reference 373 explains the 2027 Singapore–Cambridge SEC G3 Computing K349 Module 3.2.4 lookup requirements. It teaches VLOOKUP, HLOOKUP, INDEX and MATCH, exact versus approximate matching, sorted threshold tables, range anchoring, first-match behaviour, missing-key handling and two-dimensional lookup. The worked examples use fictional product codes and non-official classification bands so students can learn the mechanism without mistaking invented categories for Singapore examination grades.
This is an independent eduKateSG educational reference, not an official MOE, SEAB or Cambridge publication. Official K349 learning outcomes and listed functions govern assessment scope. The example thresholds are invented teaching data rather than real grade cutoffs or institutional decisions. Verify exact input, table order and referenced cells before using any lookup result to make a meaningful decision.
1. The official 2027 K349 lookup boundary
Module 3.2.4 asks learners to look up values in unsorted vertical and horizontal tables using exact matching; perform approximate lookups on sorted tables; classify values by ranges with a secondary table; find values at the intersection of rows and columns; and calculate the relative position of a value in a range. The examinable functions include HLOOKUP, INDEX, MATCH and VLOOKUP.
These are related but distinct tasks. A simple identifier lookup should not silently become an approximate category search. A two-dimensional table may need a row and a column position rather than a single return-column number. The first question is always what information the input identifies and what the output should represent.
2. A lookup table is a relationship, not a list of guesses
Imagine a fictional item table with codes P01, P02, P03 and P04 in column A, with quantities 10, 20, 30 and 40 in column B. A lookup for P03 should retrieve 30 because that is the value stored alongside the exact code.
The table’s purpose is to connect the two fields. If the wrong quantity is stored beside P03, a formula can retrieve it correctly while the data remain wrong. Lookup correctness and source-data accuracy must therefore be evaluated separately. Always establish which column contains keys and which column holds the required results.
3. VLOOKUP searches vertically
VLOOKUP can search the first column of a selected table range and return a value from a specified column in that range. For a vertical code-to-quantity table, =VLOOKUP(“P03”,$A$2:$B$5,2,FALSE) looks for P03 in column A and retrieves 30 from the second column.
The third argument counts columns within the selected table, beginning with one. It does not mean spreadsheet column B has an absolute column number of two in every possible selected range. A shifted table range changes which column index refers to which field.
4. FALSE requests exact matching
With VLOOKUP and HLOOKUP, specifying FALSE for the final matching argument requests an exact match. This is appropriate for unique codes, labels or other identifiers where a neighbouring string or number should not count as the same item.
A frequent error is omitting the match argument when an exact search was required. Depending on the function, the default may be approximate, returning a result that looks plausible but does not belong to the requested code. Writing FALSE explicitly makes the intended match policy clear.
5. Exact matching does not require sorting
An exact VLOOKUP can retrieve a key from a table even if the keys are not ordered, provided the key is present and the lookup range is configured correctly. This is one reason exact matching suits fictional item codes such as P03 or P01.
The absence of a sorting requirement should not be confused with permission to leave duplicate or malformed identifiers unchecked. An exact match can still return an unsuitable source value if multiple entries use the same key or if the requested code was mistyped.
6. An absent key is not automatically a zero value
If the fictional table contains P01 through P04 but the lookup asks for P99, an exact lookup will ordinarily return #N/A. It does not produce quantity zero unless another rule explicitly converts absence into that result.
A real inventory system should distinguish an unknown code from a known item with zero units. The same distinction appears in Python dictionaries. A missing-key error is useful evidence about the source or request; hiding it without explanation can cause downstream decisions based on invented data.
7. Approximate matching is a threshold operation
For a vertical classification table with starting thresholds 0, 40, 70 and 85, a lookup value 78 matches the row beginning at 70 when the thresholds are sorted ascending and the VLOOKUP approximate-match convention is used. A fictional label such as ‘Proficient’ could be associated with that row.
This is not the same as an exact search for the number 78; 78 need not appear as a key. The rule selects the largest threshold not exceeding the lookup value, subject to the matching conditions. The interpretation depends on the table’s category definitions, not on a universal grading system.
8. Sort the approximate lookup table correctly
The conventional approximate VLOOKUP and HLOOKUP patterns require an appropriately ascending lookup row or column for reliable threshold matching. A table with thresholds 70, 0, 85, 40 in that order is unsuitable for this use, even though all four numbers are present.
Students should not assume a spreadsheet will recognise an unsorted table and silently repair the order. An approximate result from an invalidly ordered table can be misleading. Before evaluating the formula, inspect the first lookup column or row and verify its sorted structure.
9. A threshold below the first key is special
With minimum threshold zero, a negative lookup value lies below the first defined range. An approximate lookup may yield #N/A because no threshold is at or below that input. This should not be reinterpreted as the lowest category without an explicit design rule.
If the domain forbids negative scores, the error could be useful evidence of invalid input. If the domain allows them, the threshold table may be incomplete. The student should explain the difference and avoid inventing a category when the mapping does not cover the requested value.
10. Upper boundaries belong to the next band
For starting thresholds 0, 40, 70 and 85, a value exactly 70 belongs to the row beginning at 70, not to the row beginning at 40. A value of 69 belongs to the earlier threshold 40. The boundary tests therefore reveal the matching convention more clearly than values far from any cutoff.
Write a tiny expected-results table for 39, 40, 69, 70, 84 and 85 before using the feature. When a student can justify those six cases, the underlying range rule is much harder to confuse with ordinary nearest-number matching.
11. Approximate lookup does not mean nearest distance
A common misconception is that approximate VLOOKUP finds whichever threshold is numerically closest. In the sorted lower-bound scheme, value 84 maps to 70 rather than 85, even though 85 is only one unit away.
That difference is vital. Range classification uses an interval rule rather than mathematical proximity. Students should avoid describing it as ‘the nearest row’ and instead say that it selects the appropriate lower-bound threshold for the specified value.
12. HLOOKUP searches a horizontal table
HLOOKUP searches a table’s first row and retrieves a value from a specified row within that table range. Suppose fictional codes P01, P02 and P03 appear across the top row, with quantities 10, 20 and 30 underneath. An exact HLOOKUP for P02 using return row two gives 20.
The function is conceptually similar to VLOOKUP but uses the opposite orientation. The key must be in the top row of the selected range. A student who applies vertical assumptions to a horizontal layout may return the wrong field or use an incorrect return-index argument.
13. INDEX retrieves a value at a position
INDEX can return the value at a specified row and optionally column position within a cell range. For B2:B5 containing 10, 20, 30 and 40, =INDEX(B2:B5,3) returns 30 because the third element of that selected range is 30.
This is a position-based operation, not an automatic search for an identifier. The row number is relative to the specified range. It is one-based for the intended spreadsheet function usage, which differs from Python’s zero-based list indexing.
14. MATCH returns a relative position
MATCH searches a specified one-dimensional range and returns the relative position of a matching value. For codes P01, P02, P03, P04 in A2:A5, =MATCH(“P03”,A2:A5,0) returns 3 because P03 is third within that range.
The final zero requests exact matching. The returned number is a position, not the quantity associated with P03. This is the key distinction: MATCH finds where an item is in the lookup range; INDEX can retrieve the value at a chosen position.
15. INDEX and MATCH can work together
The expression =INDEX($B$2:$B$5,MATCH(“P03”,$A$2:$A$5,0)) uses MATCH to find position three for P03, then INDEX retrieves the third value from the quantity range, giving 30. The two ranges should align row by row.
This arrangement is useful when key and return fields are stored in different selected columns. If the lookup and return ranges do not correspond, a valid index can point to the wrong record. Verify range alignment as carefully as the matching mode.
16. Two-dimensional INDEX needs row and column context
For a table of fictional monthly quantities, a student may need the value where a named item row intersects a named month column. INDEX can retrieve from a rectangular data region if supplied with the correct relative row and column positions.
Those positions can be obtained through separate MATCH operations on the item labels and month headers. The concept is an intersection: find the correct row, find the correct column, then retrieve the data at their meeting point. A good diagram of the table is often clearer than a memorised formula.
17. Absolute references protect a copied lookup range
When a formula is copied down rows, a relative table range such as A2:B5 may shift to A3:B6. That would exclude the first lookup entry and include an unintended new row. Using $A$2:$B$5 fixes the selected table area when all formulas should refer to the same lookup data.
The lookup value reference may remain relative, such as D2, so that each row asks about a different code. A correctly anchored table and correctly changing lookup value are both required. Missing one dollar sign can create wrong answers only in later copied rows.
18. Errors deserve diagnosis before concealment
If a lookup gives #N/A, check the key, match mode, orientation, source range, sorting requirements, and whether the value exists or is below the minimum threshold. Do not immediately replace the error with zero or another invented category.
The most useful debugging method compares the query with the exact source keys. If the formula returns a result but the value is unexpected, inspect duplicates and range alignment. A true problem can occur even without an obvious error message.
Fictional vertical lookup table:
A2:P01 B2:10
A3:P02 B3:20
A4:P03 B4:30
A5:P04 B5:40
=VLOOKUP("P03",$A$2:$B$5,2,FALSE) -> 30
=MATCH("P03",$A$2:$A$5,0) -> 3
=INDEX($B$2:$B$5,3) -> 30
=INDEX($B$2:$B$5,MATCH("P03",$A$2:$A$5,0)) -> 30
Fictional ascending threshold table:
0 Basic; 40 Developing; 70 Proficient; 85 Advanced
=VLOOKUP(78,range,2,TRUE) -> ProficientA twelve-stage Key–Table–Match protocol
This independent teaching routine is not an official examination marking rubric. It provides a reliable order for reasoning about vertical, horizontal and two-dimensional lookups. The purpose is to stop students from memorising a function name without checking the table orientation, key location, matching mode and return-field alignment.
Stage 1 — Identify the requested output
State whether the task asks for a specific item value, a band determined by a threshold, a row position or an intersection of two labels.
Stage 2 — Inspect the lookup table
Locate its top-left corner, record the key field, and confirm what each row or column represents.
Stage 3 — Determine the orientation
Use vertical reasoning for a first-column key or horizontal reasoning for a first-row key. Do not mix the two.
Stage 4 — Choose exact or approximate matching
Exact matching requires a matching identifier. Approximate threshold matching uses an ordered range convention, not nearest numerical distance.
Stage 5 — Check the sorting condition
For conventional approximate VLOOKUP/HLOOKUP, verify that threshold keys follow the required ascending order.
Stage 6 — Verify the search range
Identify the precise range and, for copied formulas, whether it should be anchored using absolute references.
Stage 7 — Choose the return field
Count the column or row position relative to the selected table range; do not substitute an unrelated worksheet coordinate.
Stage 8 — Predict a sample result by hand
Locate a known key or threshold and state its corresponding return value without invoking the spreadsheet function.
Stage 9 — Test a missing or out-of-range key
Ask what happens if the identifier is absent or the numeric input is below the smallest threshold.
Stage 10 — Check threshold boundaries
Evaluate values immediately below, at and above each important cutoff; the rule must be consistent with the table.
Stage 11 — Trace any two-dimensional query
Find the matching row and column positions separately before using INDEX at their intersection.
Stage 12 — Explain the result and limitation
State the returned value, why the particular record matched and what the lookup cannot establish about source-data accuracy.
Twelve original spreadsheet lookup cases
Worked case 1 — An exact vertical code
Given keys P01–P04 with quantities 10,20,30,40, exact VLOOKUP for P03 returns 30. P03 matches the third record and 30 is in the selected return column.
Worked case 2 — An absent identifier
With the same table, exact lookup P99 ordinarily returns #N/A. This indicates the code was not found, not that its quantity is known to be zero.
Worked case 3 — Unsorted exact keys
An exact-match table contains P03, P01 and P02 in that order. An exact VLOOKUP for P01 can still locate its row; exact matching does not require the key column to be sorted.
Worked case 4 — Wrong return-column index
The table range begins at C2 and contains key, quantity and note in columns C, D and E. A return index of 2 means column D, not worksheet column B. Count within the selected table.
Worked case 5 — A duplicate identifier
If P03 occurs twice with different quantities, ordinary VLOOKUP exact matching finds the first matching record. The result may be ambiguous for a business requirement even though the function behaved as defined.
Worked case 6 — An approximate score 78
A sorted threshold table begins at 0,40,70,85. The value 78 belongs to the row beginning at 70, because 70 is the greatest threshold not exceeding 78.
Worked case 7 — The threshold 70 exactly
With the same bands, 70 matches the row beginning at 70. The previous row’s range ends before that threshold, not at or after it.
Worked case 8 — A score of 69
With lower bounds 0,40,70,85, value 69 matches 40, the greatest qualifying threshold. It does not match 70 merely because 70 is numerically closer.
Worked case 9 — A score below the minimum
If the first lower bound is 0, the value -1 has no qualifying threshold in the table. An approximate lookup can produce #N/A instead of a fictional lowest-category result.
Worked case 10 — An unsorted approximate table
Thresholds 70,0,85,40 are not ascending. A conventional approximate VLOOKUP cannot be trusted to implement the intended lower-bound classification on this order.
Worked case 11 — A horizontal item table
Codes P01, P02 and P03 appear in the top row, with quantities 10,20,30 below. HLOOKUP for P02 with return row 2 and exact match returns 20.
Worked case 12 — INDEX and MATCH together
For A2:A5 containing P01–P04 and B2:B5 containing 10–40, MATCH for P03 with mode zero gives position three, and INDEX of the quantity range at that position gives 30.
Twelve further lookup cases: position, range and data quality
Worked case 13 — A two-dimensional lookup
A fictional table has rows C01, C02, C03 and columns Jan, Feb, Mar. If row C02 contains values 21,24,26, INDEX at the second row and third month column gives 26, provided the selected data range begins at the proper corner.
Worked case 14 — Finding two positions
MATCH(“C02”,A2:A4,0) gives the second position if C02 is the second code. MATCH(“Mar”,B1:D1,0) gives the third position if Mar is the third header. Those coordinates together identify one intersection.
Worked case 15 — INDEX range starts elsewhere
If a data block begins at D10, the first INDEX row refers to row 10 within that block, not worksheet row one. The position is relative to the selected range.
Worked case 16 — One search range is misaligned
Codes A2:A5 are paired with values B3:B6 by mistake. INDEX may return a value from the wrong source row even when MATCH found the correct relative position. Align the arrays.
Worked case 17 — A copied range shifts
A lookup intended to search $A$2:$B$5 is written using A2:B5 and copied down. Later formulas may search A3:B6 and omit the first record. Anchor the table when it must remain fixed.
Worked case 18 — The lookup value changes per row
With identifiers listed in D2:D11, formulas filled down should normally use D2, D3 and so on as the current lookup values. Fixing $D$2 accidentally makes every row request the same identifier.
Worked case 19 — MATCH mode zero
MATCH(“P03”,A2:A5,0) requests exact matching and returns the third relative position for the specified ordered sample. A result of 3 is not the product quantity 30.
Worked case 20 — A text code has leading zeros
The strings ‘P03’ and ‘P3’ are not identical identifiers. An exact lookup should not silently remove or invent a zero merely because a human reader thinks the codes refer to the same item.
Worked case 21 — A category changes at the threshold
If a lower-bound table starts at 40,70,85, the value 70 belongs to the 70 row. A score of 69 remains in the 40 row. Testing both values exposes a classification-boundary error.
Worked case 22 — Approximate matching is not rounding
The input 84 in a threshold table 0,40,70,85 selects the lower bound 70 rather than the closer number 85. The rule is interval classification, not arithmetic rounding.
Worked case 23 — An exact lookup with duplicates
If the first P02 row gives 20 and a later P02 row gives 99, ordinary exact VLOOKUP returns the first matching result. The duplicate should be investigated before treating the table as a unique-key directory.
Worked case 24 — A missing month header
If the two-dimensional table has only Jan and Feb headers, MATCH(“Mar”,…) with exact mode cannot produce a valid column position. The query is outside the supplied header set; do not invent a missing month’s quantity.
Connected investigations: constructing reliable lookups
An exact product-code table
Build a fictional directory of four item codes and quantities. Each code must be unique, and the lookup should return the value associated with precisely that code. Test the first code, a middle code, the last code and a code not present. This distinguishes successful retrieval from the missing-key error.
An excellent student also checks that no source rows have been duplicated or shifted. A lookup that returns a number is only reliable if the table’s code-to-value relationships are accurate. The formula cannot guarantee that a human typed the correct quantity next to each code.
A range-classification table is different
Create invented classification thresholds at 0, 40, 70 and 85. Place them in ascending order and assign neutral fictional labels. Test 39, 40, 69, 70, 84 and 85, stating for each the greatest threshold not above the input. These six checks cover four regions and several exact boundaries.
A student should explain why the input 84 matches the row beginning at 70 despite being numerically closer to 85. This guards against the common but false description of approximate lookup as nearest-neighbour search. An approximate table encodes a defined interval mapping.
What a missing result can tell us
If an exact lookup of P99 returns #N/A, at least one necessary condition for a successful match has failed. The key may be genuinely absent, misspelled, of the wrong data type or looked up in an incorrect source range. In a threshold table, an input below the smallest lower bound can also produce an error.
A useful response checks the requested identifier, the match mode, the selected table and the source values. Replacing the error with zero may be inappropriate because it conflates an unknown record with a known record whose stored quantity is zero. The distinction matters in educational records and practical systems alike.
Why INDEX plus MATCH is worth learning
VLOOKUP is convenient when a key appears in the first column of a well-formed table and the return value is in a later column. INDEX with MATCH separates locating the key’s position from retrieving a value, which can make table relationships easier to understand when the desired fields are elsewhere.
The method does not eliminate the need for correct ranges. If the MATCH range includes one set of rows but the INDEX range begins on a different row, the returned position can identify the wrong record. Build a small example where the row numbers are written visibly, then compare the two arrays before constructing the full formula.
Two-dimensional retrieval needs two axes
A fictional matrix of three item codes by three months has nine values. Asking for C02 in March requires both a row identity and a month identity. A match on the row alone does not determine one cell, and neither does a match on the column alone.
The learner should point to the intersection and name the expected value before writing INDEX. Then determine the relative row and column positions inside the data block. This exercise trains spatial table reasoning and reduces the tendency to guess argument numbers from absolute worksheet coordinates.
Copied formulas must retain the right ranges
A working lookup in row two is not enough. When students fill formulas down a worksheet, the lookup value may need to move from D2 to D3, but the source directory should remain $A$2:$B$5. These are two different reference policies inside one formula.
After filling down, inspect the formula in the last row and compare it with the first. This catches an unanchored source range or an accidentally fixed lookup value. A small test table with expected outputs is stronger evidence than trusting the number of visible non-error cells.
Tutoring for retrieval rather than memorisation
Ask a student to explain which part of VLOOKUP means ‘find the key’, which argument means ‘return this field’, and why FALSE specifies exact matching. Then ask how the mechanism changes for HLOOKUP and why MATCH returns a position rather than the value itself.
Parents can practise with a simple printed table of invented codes and quantities. The student can locate a requested row without using a computer, then explain how the spreadsheet would perform the same task. Finally introduce one missing code and one threshold table to test the difference between lookup modes.
A reliable final lab audit
Before saving a spreadsheet, check the lookup value’s type and spelling, selected range, orientation, match mode, threshold sorting and returned position. If formulas were filled down, inspect the final row. Verify at least one normal example and one error or boundary case independently.
Do not treat a coloured result or completed formula as proof of correctness. A misaligned table or duplicated key can produce a normal-looking value. The goal is a result that corresponds to a specific, defensible relationship in the actual source data.
Frequently asked questions
What is exact matching?
A lookup requires the requested identifier or value to match a key, rather than selecting a nearby threshold.
Can an exact VLOOKUP use an unsorted table?
Yes. Exact matching does not require the lookup keys to be sorted in ascending order.
What does approximate matching do?
In a suitable sorted threshold table, it selects the qualifying lower-bound row rather than the numerically nearest threshold.
Why must approximate keys be sorted?
Conventional approximate VLOOKUP and HLOOKUP rely on an appropriately ascending key order for their threshold semantics.
Does VLOOKUP find any column?
It searches the first column of the selected table range and returns the specified column within that range.
What does HLOOKUP search?
The first row of a selected horizontal range, returning an entry from the specified row.
What does MATCH return?
A relative position in a one-dimensional range, not automatically the corresponding item value.
What does INDEX return?
A value at a specified relative row or column position within a chosen range.
What does #N/A mean in a missing-code case?
The search did not find the requested match under the supplied range and matching conditions; it is not the number zero.
How do I check copied lookup formulas?
Ensure the lookup value changes to the intended row while the source table remains anchored when required.
What if keys are duplicated?
Ordinary exact lookup may return the first match, so duplicate data can make the result unsuitable for a unique-code requirement.
What is the best final check?
Locate the record or threshold by hand, confirm the expected return value, then compare it with the spreadsheet’s actual result.
Official K349 syllabus and connected eduKateSG references
Read the official 2027 SEC G3 Computing K349 syllabus, especially 3.2.4 and the examinable function list. Related learning: Reference 311 — Spreadsheet Formulas and Lookups, Reference 372 — Goal Seek, the MOE V3.0 library and the immutable Clementi teaching reference.
Editorial review: 29 October 2026, Singapore. All lookup data, category names and thresholds are invented teaching material. The transferable skill is to state whether the task requires an exact key or a range classification, then verify the source, order, boundaries and returned value.