Lookup Functions

The previous two lessons assumed something: that the code in the visit record is found in the subscriber table and that the consumption against it is fetched. What does that job is a lookup function, and the lookup answers two questions separately: which row, which column. The second is answered either by a number or by a header name, and that answer is the whole lesson. The same lookup is written in four forms, and all four produce the same number; then a column is inserted.

• BO19. When a column is inserted, cell references shift, but number constants in the formula text do not. This is the rule at the center of the model, and it is written in the insertColumn function.
• BO20. A single-row or single-column range is expanded into a flat array, a rectangular range into an array of rows. vlookup searches for its key in the first column of the range.
• BO21. The visit codes are taken to have gone through the cleaning decision of the previous lesson; the only thing measured here is the lookup.

Two Versions of the Lookup

The block below builds the five-column subscriber table, the visit list, and two versions of the lookup.

// lookup.mjs — spreadsheet MODEL: lookup functions and column insertion.
// When a column is inserted, CELL REFERENCES shift, NUMBER CONSTANTS in the formula do not.
export const SEED = 20260602, ROWS = 60, VISITS = 48;
const rng = (t) => { let s = t >>> 0;
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const REGION = ["north", "south", "east", "west", "central"];

const REF = /(\$?)([A-Z])(\$?)(\d+)/g;
const parse = (a) => ({ c: a.charCodeAt(a[0] === "$" ? 1 : 0) - 65, r: +a.replace(/\D/g, "") });
const cellName = (c, r) => String.fromCharCode(65 + c) + r;
const RANGE = /(\$?[A-Z]\$?\d+):(\$?[A-Z]\$?\d+)/g;
// A single-row or single-column range is expanded into a flat array, a rectangular range into an array of rows.
const expand = (a, b, H) => { const x = parse(a), y = parse(b), out = [];
  const flat = x.c === y.c || x.r === y.r;
  for (let r = x.r; r <= y.r; r++) { const row = [];
    for (let c = x.c; c <= y.c; c++) row.push(value(H, cellName(c, r)));
    if (flat) out.push(...row); else out.push(row); }
  return out; };
export const shift = (f, dC, dR) => f.replace(REF, (_, a, s, b, r) =>
  (a ? "$" + s : String.fromCharCode(s.charCodeAt(0) + dC)) + (b ? "$" + r : String(+r + dR)));

const find = (k, d, kind) => {
  if (kind === 0) return d.indexOf(k) + 1;             // exact match: 0 if absent
  let i = 0;                                           // approximate match: the last one not greater than k
  for (let j = 0; j < d.length; j++) if (d[j] <= k) i = j + 1;
  return i;
};
const FUNCS = {
  match: (k, d, kind) => find(k, d, kind),
  index: (d, s, c) => (c === undefined ? d[s - 1] : d[s - 1][c - 1]),
  vlookup: (k, T, n, kind) => { const i = find(k, T.map((r) => r[0]), kind);
    return i ? T[i - 1][n - 1] : "#N/A"; },
};
export function value(H, name) {
  const v = H[name];
  if (v === undefined) return 0;
  if (typeof v !== "string" || v[0] !== "=") return v;
  const expr = v.slice(1)
    .replace(RANGE, (_, a, b) => JSON.stringify(expand(a, b, H)))
    .replace(REF, (m) => JSON.stringify(value(H, m.replace(/\$/g, ""))));
  return Function(...Object.keys(FUNCS), `return (${expr});`)(...Object.values(FUNCS));
}

// Column insertion: the cells at and to the right of the position move one column, and the references in
// formulas move with them. The NUMBER CONSTANTS in the formula text do not change — this whole lesson rests on that difference.
export function insertColumn(H, at, header, make) {
  const k = at.charCodeAt(0) - 65, Y = {};
  const move = (f) => f.replace(REF, (_, d1, s, d2, r) => { const c = s.charCodeAt(0) - 65;
    return (d1 ? "$" : "") + cellName(c >= k ? c + 1 : c, "") + (d2 ? "$" : "") + r; });
  for (const [name, v] of Object.entries(H)) {
    const c = name.charCodeAt(0) - 65, r = name.slice(1);
    Y[cellName(c >= k ? c + 1 : c, r)] =
      typeof v === "string" && v[0] === "=" ? "=" + move(v.slice(1)) : v;
  }
  Y[at + "1"] = header;
  for (let i = 0; i < ROWS; i++) Y[at + (i + 2)] = make(i);
  return Y;
}

export const VERSIONS = [
  ["vlookup, column no. 3", "=vlookup(P2,$A$2:$E$61,3,0)"],
  ["vlookup, by header", '=vlookup(P2,$A$2:$E$61,match("consumption",$A$1:$E$1,0),0)'],
  ["match-index, by column", "=index($C$2:$C$61,match(P2,$A$2:$A$61,0))"],
  ["match-index, by header",
    '=index($A$2:$E$61,match(P2,$A$2:$A$61,0),match("consumption",$A$1:$E$1,0))'],
];

export function sheet() {
  const r = rng(SEED), H = {};
  H.A1 = "subscriber"; H.B1 = "region"; H.C1 = "consumption"; H.D1 = "reading"; H.E1 = "amount";
  for (let i = 0; i < ROWS; i++) {
    const s = i + 2;
    H["A" + s] = "AB-" + String(1041 + i * 7).padStart(4, "0");
    H["B" + s] = REGION[Math.floor(r() * 5)];
    const t = Math.round((2 + r() * r() * 78) * 10) / 10;
    H["C" + s] = t; H["D" + s] = Math.round(t); H["E" + s] = Math.round((t * 8.4 + 25) * 100) / 100;
  }
  // Visits table: the codes are cleaned (previous lesson), each record belongs to one subscriber.
  const z = rng(SEED + 2);
  const order = Array.from({ length: ROWS }, (_, i) => i);
  for (let i = ROWS - 1; i > 0; i--) { const j = Math.floor(z() * (i + 1));
    [order[i], order[j]] = [order[j], order[i]]; }
  H.P1 = "visit code";
  for (let i = 0; i < VISITS; i++) H["P" + (i + 2)] = H["A" + (2 + order[i])];
  return H;
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const H = sheet();
  const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
    : String(v).padEnd(B[0]))).join("");
  const y = g([8, 12, 10, 12, 12]);
  console.log(y(["cell", "subscriber", "region", "consumption", "amount"]));
  for (const s of [2, 3, 61])
    console.log(y(["A" + s, H["A" + s], H["B" + s], H["C" + s], H["E" + s]]));
  console.log(`\nHeaders A1:E1 = ${[..."ABCDE"].map((c) => H[c + "1"]).join(", ")}; ` +
    `"consumption" is column 3. Visit code P2 = ${H.P2}.`);
  for (const [name, f] of VERSIONS.slice(0, 2))
    console.log(`${name}\n  ${f}\n  -> ${value({ ...H, W2: f }, "W2")} m3`);
}

cell      subscriber    region consumption      amount
A2           AB-1041     south         9.8      107.32
A3           AB-1048      east        71.6      626.44
A61          AB-1454      east        21.2      203.08

Headers A1:E1 = subscriber, region, consumption, reading, amount; "consumption" is column 3. Visit code P2 = AB-1286.
vlookup, column no. 3
  =vlookup(P2,$A$2:$E$61,3,0)
  -> 28.9 m3
vlookup, by header
  =vlookup(P2,$A$2:$E$61,match("consumption",$A$1:$E$1,0),0)
  -> 28.9 m3

Both versions give 28.9 m³ and both use the same function. The only difference is how the “consumption” column is pointed to. In the first, that column is a number: 3. Where the number came from is not written in the formula; the person who wrote it counted the headers and put the number they counted into the formula. In the second, the column is a name and is searched for again in the header row every time.

At this point the distinction looks like a difference of style; a check that compares results cannot tell the two apart. What tells them apart is how the sheet reacts to change.

A Column in the Middle of the Table

The block below builds the four versions, then inserts a “previous period” column between B and C; its values are of the same magnitude as the existing consumption.

// breakage.mjs — the same lookup in four versions; what happens when a column is inserted in the middle of the table.
import { VERSIONS, sheet, value, shift, insertColumn, VISITS } from "./lookup.mjs";

const H = sheet();
const fill = (K, f) => { const M = { ...K };
  for (let i = 0; i < VISITS; i++) M["W" + (i + 2)] = shift(f, 0, i);
  return M; };
// A "previous period" column is inserted between B and C: its values are of the same magnitude as consumption.
const PREVIOUS = (i) => Math.round(H["C" + (i + 2)] * 9) / 10;
const read = (M, s) => Array.from({ length: VISITS }, (_, i) => value(M, s + (i + 2)));
const count = (d) => d.filter((v) => typeof v === "number").reduce((a, b) => a + b, 0);
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");

const y = g([26, 14, 14, 10, 8, 10]);
console.log(y(["version", "total before", "total after", "changed", "errors", "silent"]));
for (const [name, f] of VERSIONS) {
  const A = read(fill(H, f), "W");
  const E = insertColumn(fill(H, f), "C", "previous period", PREVIOUS);
  const B = read(E, "X");                             // the W column became X after the insertion
  const changed = B.filter((v, i) => v !== A[i]).length;
  const errors = B.filter((v) => typeof v !== "number").length;
  console.log(y([name, count(A).toFixed(1), count(B).toFixed(1), changed, errors, changed - errors]));
}
// The same breakage becomes visible when the inserted column carries an unreasonable number.
console.log("");
for (const [name, make] of [["previous period", PREVIOUS], ["meter no", (i) => 4100 + i * 3]]) {
  const B = read(insertColumn(fill(H, VERSIONS[0][1]), "C", name, make), "X");
  console.log(`If the inserted column is "${name}", the broken lookup totals ${count(B).toFixed(1)} m3, ` +
    `first value ${B[0]}.`);
}

version                     total before   total after   changed  errors    silent
vlookup, column no. 3             1204.8        1084.6        48       0        48
vlookup, by header                1204.8        1204.8         0       0         0
match-index, by column            1204.8        1204.8         0       0         0
match-index, by header            1204.8        1204.8         0       0         0

If the inserted column is "previous period", the broken lookup totals 1084.6 m3, first value 26.
If the inserted column is "meter no", the broken lookup totals 201081.0 m3, first value 4205.

One of the four versions broke: 48 of 48 records changed, and none of them raised an error. The broken version still returns a number, and the number is still of the size of a consumption. The report shows 1084.6 instead of 1204.8 m³; the difference is 10%, small enough for a check total to approve.

The cause of the break is not a function but a constant: the second version, which reads the column number from the header, uses the same function and did not break. Which position a column is in is not a fact but a moment; when a column is inserted, the moment passes and the number stays.

The last two lines measure the visibility of the error. Had the inserted column carried meter numbers, the same broken formula would have produced 201081.0 m³ and been caught; when it carries previous-period consumption, it produces 1084.6 m³ and is not caught. The visibility of an error is measured not by its size but by what it resembles.

When the Order Changes

The second constant of the lookup is the last argument: exact match or approximate. The block below measures this decision under two sort orders, then scans the four versions.

// sort.mjs — approximate match depends on sort order; and reading the decision back from the formula.
import { VERSIONS, sheet, value, shift, ROWS, VISITS } from "./lookup.mjs";

const H = sheet();
// The table is re-sorted by region: rows swap places, range names do not change.
const sorted = (K) => {
  const s = Array.from({ length: ROWS }, (_, i) => i + 2)
    .sort((a, b) => (K["B" + a] < K["B" + b] ? -1 : K["B" + a] > K["B" + b] ? 1
      : K["A" + a] < K["A" + b] ? -1 : 1));
  const Y = { ...K };
  s.forEach((old, j) => { for (const c of "ABCDE") Y[c + (j + 2)] = K[c + old]; });
  return Y;
};
const fill = (K, f) => { const M = { ...K };
  for (let i = 0; i < VISITS; i++) M["W" + (i + 2)] = shift(f, 0, i);
  return M; };
const read = (M) => Array.from({ length: VISITS }, (_, i) => value(M, "W" + (i + 2)));
const g = (B) => (h) => h.map((v, j) => (j ? String(v).padStart(B[j])
  : String(v).padEnd(B[0]))).join("");

const S = sorted(H);
const CORRECT = read(fill(H, "=vlookup(P2,$A$2:$E$61,3,0)"));
const y = g([20, 16, 10, 10, 8]);
console.log(y(["table sort order", "last argument", "total", "wrong", "errors"]));
for (const [name, K] of [["by subscriber code", H], ["by region", S]])
  for (const [kind, last] of [["0 exact match", "0"], ["1 approximate", "1"]]) {
    const d = read(fill(K, `=vlookup(P2,$A$2:$E$61,3,${last})`));
    const t = d.filter((v) => typeof v === "number").reduce((a, b) => a + b, 0);
    console.log(y([name, kind, t.toFixed(1), d.filter((v, i) => v !== CORRECT[i]).length,
      d.filter((v) => typeof v !== "number").length]));
  }

// Read-back scan: the embedded numbers and header names in the formula text are counted.
const place = (f) => /"[a-z]+"/.test(f) ? "header name"
  : /index\(\$[A-Z]\$\d+:\$[A-Z]\$\d+,/.test(f) ? "cell range" : "embedded number";
const z = g([26, 16, 10, 14]);
console.log("\n" + z(["version", "column decision", "constants", "match type"]));
for (const [name, f] of VERSIONS) {
  const rest = f.replace(/\$?[A-Z]\$?\d+/g, "");      // range and cell names are not counted
  const consts = (rest.match(/\d+/g) ?? []);
  console.log(z([name, place(f), consts.length, consts.at(-1) + " (embedded)"]));
}

table sort order       last argument     total     wrong  errors
by subscriber code     0 exact match    1204.8         0       0
by subscriber code     1 approximate    1204.8         0       0
by region              0 exact match    1204.8         0       0
by region              1 approximate     890.9        34       0

version                    column decision constants    match type
vlookup, column no. 3      embedded number         2  0 (embedded)
vlookup, by header             header name         2  0 (embedded)
match-index, by column          cell range         1  0 (embedded)
match-index, by header         header name         2  0 (embedded)

The first two rows of the first table set a trap: in a table sorted by subscriber code, exact match and approximate match give the same result, so the person choosing sees no difference. The third and fourth rows turn the sort order to region — an ordinary job for someone who wants to look at the regional breakdown. Exact match does not budge; approximate match returns a different row in 34 of 48 records, the total drops to 890.9 m³, and again there is no error.

The second table applies the course’s measure to the lesson. The column decision can sit in three places: in an embedded number, in a cell range, in a header name — and the only version that broke is the one whose decision is an embedded number. But the last column says something more disturbing: in all four, the match type is an embedded literal. If that 0 were a 1, no error would appear, and on the day the table was re-sorted 34 records would silently change.

The rule maintained since the first lesson of the course is completed here: a decision embedded in a cell is an unwritten decision. The column number and the match type are both decisions, and both sit disguised as a number.

Summary

• A lookup answers two questions separately — which row, which column; the second answer can be a number or a header name, and the two produce the same result.
• When a column is inserted, cell references shift and number constants do not: only one of four versions broke, returned a wrong value in 48 of 48 records, and raised no error at all.
• The total became 1084.6 instead of 1204.8 m³; the same breakage would have shown up as 201081.0 m³ with a different column. What breaks is not the function but the embedded column number.
• Approximate match depends on sort order and does not reveal this as long as the table is sorted: after sorting by region, 34 records silently changed.
• In all four versions the match type is an embedded number; the course’s rule is completed here — a decision embedded in a cell is an unwritten decision.

Next Step

Over six lessons this topic measured cell-level decisions: reference type, counting criterion, condition boundary, cleaning rule, day-counting rule, lookup form. In each lesson a number was placed next to the decision that produced it and the number of steps in which the decision could be read back was counted. But all of these measurements looked at individual cells; none produced a number that looks at the whole table.

A more basic question was never asked either: is this table clean. Throughout six lessons it was assumed that the 60 rows were 60 distinct subscribers and that each row appeared once. The next topic takes up both gaps together: it measures how many rows remain when the deduplication key changes and how many values the missing-value decisions assign to the same mean, then builds the summaries that look at the whole table.