BISM1201 Chap.8 Lookups, Dynamic Arrays and Reporting
Lookups, Dynamic Arrays and Reporting
Lookup formulas connect one dataset to a reference source. Exact match is appropriate for identifiers; approximate match is appropriate for ordered bands and requires sorted thresholds. VLOOKUP searches from the first table column and returns by column position. XLOOKUP names the lookup and return arrays, can search in either direction and can specify a missing result.
Dynamic-array functions turn a rule into a live report: FILTER selects rows, SORT orders them, UNIQUE produces distinct values and SEQUENCE generates a series. Spill ranges must remain clear, and reports still require source validation, refresh discipline and interpretation.
What this chapter covers
- 01
Lookup keys and reference-table quality
- 02
Exact and approximate match logic
- 03
VLOOKUP and XLOOKUP differences
- 04
FILTER, SORT, UNIQUE and SEQUENCE
- 05
Pivot summaries, reporting controls and interpretation
Building a live training-priority report
- +1Validate status, readiness and date fields before applying a reporting rule.
- +1Use FILTER with both active status and readiness below 3.
- +1Use SORT on the filtered result by review date.
- +1Use UNIQUE on the relevant department column for a distinct action list.
- +1Define an empty-result message and interpret the output as review evidence, not an automatic judgement.
Key terms
- Exact match
- A lookup mode that returns a result only when the key equals a reference value, appropriate for identifiers.
- Approximate match
- A lookup mode that assigns a value to an ordered interval, appropriate for correctly sorted threshold tables.
- Lookup key
- The value used to locate a related record, expected to follow defined type, cleanliness and uniqueness rules.
- Spill range
- The set of cells populated by one dynamic-array formula and resized as the returned result changes.
- Dynamic array
- A formula result that returns several values from one maintained expression and updates with its source.
- Pivot summary
- An interactive grouping and aggregation of records used to compare counts, totals or averages by categories.
Lookups, Dynamic Arrays and Reporting FAQ
When should a lookup use exact match?
Use exact match for product codes, employee identifiers and other keys where a nearby value would be wrong. Validate data type and uniqueness, and define a clear not-found result rather than accepting a silent false match.
What makes approximate match safe?
The business rule must use ordered bands, the threshold table must be sorted correctly, and boundary values must be tested. Approximate match is interval logic, not permission to return something merely close.
Why does a dynamic-array formula show a spill error?
The intended output area may contain values, merged cells or another obstruction. Clear the correct spill area and preserve the one maintained formula instead of copying static results that will stop updating.
What control does a PivotTable need?
Confirm its source range, refresh status and aggregation meaning. Label whether a result is a count, sum or average, and reconcile important totals before using the summary for a managerial conclusion.
Exam move
Build one exact lookup and one approximate band lookup from invented data, then test missing, duplicate and boundary cases. Create a FILTER and SORT pipeline with an explicit empty result and inspect its spill area.
Finish by grouping the same records in a PivotTable and explaining why the selected aggregation answers the business question.
For exact-match practice, deliberately include a missing key, a duplicate key, a numeric code stored as text and a value with trailing spaces. Predict each outcome before cleaning the reference table.
Decide whether the business relationship permits duplicates; do not remove them automatically if they represent distinct events. Return an action message for missing records rather than hiding them as zero.
For approximate match, draw the threshold intervals on paper and label inclusion at each boundary. Sort the table correctly and test one value below, at and above every threshold.
Explain why the formula chooses each band. This converts approximate matching from a memorised option into explicit interval logic.
Build a live reporting pipeline from source to FILTER, SORT and UNIQUE, then block part of the spill range to observe the failure. Define what an empty result should say. Create a PivotTable on the same source and compare its count, sum and average.
Refresh after adding a record and reconcile an important total. Close by writing the managerial question answered by the output and one question it cannot answer without more data.
Repeat the report after changing one source record, adding one new category and removing one lookup key. Predict every downstream change before refreshing.
Inspect whether the dynamic array grows, whether the PivotTable needs refresh and whether the missing key receives an actionable message. Reconcile the final record count to the source. This controlled change test distinguishes a genuinely maintainable report from a snapshot that happened to look correct once.
