I85PREP
⌘ K
South Indian Bank Clerk study materials
South Indian Bank Clerk study resources

The Loan Register That Broke After a Paste

South Indian Bank Clerk · Put your knowledge to work in context.

All case studies

The Loan Register That Broke After a Paste

A clerk at a branch maintains a workbook of loan accounts. Column A holds account numbers pasted from the core banking export and stored as text, and about 90 of them still carry a leading apostrophe while a few have trailing spaces. Column B holds customer names and column J is supposed to pull the sanction date by looking up each account number in a second worksheet that lists the same accounts in a different order. After the paste, 61 rows in column J show #N/A and another 14 rows show a sanction date that is one day later than the date in the sanction register. The file is now 18 MB and takes almost a minute to recalculate, because four helper columns use whole-column references such as A:A. A colleague has asked for a copy to work on at home, and last month a similar file reached the audit team with pasted values sitting where formulas should have been. The monthly review is tomorrow morning.

1. The clerk wants to rewrite the lookup so the #N/A errors disappear. Explain why simply wrapping the formula in IFERROR would be the wrong fix, and set out the diagnosis you would actually run.

Read the worked answer

IFERROR would blank out 61 rows and hide the fact that the register has stopped matching. At a monthly review, a blank sanction date is indistinguishable from a genuine gap in the data, so the error would be silently carried into the branch's ageing report. The errors must be classified before they are suppressed.

Diagnosis, in order of likelihood:

  • Type mismatch. The pasted column A is text while the source column may be numeric, so 123456 ≠ "123456". Test with =ISNUMBER(A2) and =ISNUMBER(INDEX(source!A:A,MATCH(...))).
  • Invisible characters. Trailing spaces and non-breaking spaces defeat a lookup. Test with =LEN(A2) against the visible length, and with =TRIM(CLEAN(A2))=A2.
  • Genuine absence. Some accounts may simply not be in the older source sheet. Test with =COUNTIF(source!A:A,A2&"*") or a MATCH against a cleaned key.
  • Wrong lookup range. If the source range does not begin at the account column, VLOOKUP searches the wrong field entirely.
  • The 14 shifted dates point at the default approximate match: when the exact key is missing, VLOOKUP quietly returns the previous row's value rather than an error. Check whether those account numbers exist in the source and whether they are duplicated.

2. Recommend the replacement formula and the key-cleaning step, and say why you would not use the simplest VLOOKUP.

Read the worked answer

Clean the key first, in a helper column: =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) where the entries are genuinely numeric, or =TRIM(CLEAN(A2)) where they are genuinely text. The important rule is that both sheets must be cleaned the same way - cleaning only the summary sheet reproduces the same mismatch in reverse.

Then look up with an exact match:

=IFERROR(XLOOKUP(TRIM(A2), TRIM(source!$A$2:$A$1300), source!$E$2:$E$1300, "NOT IN SOURCE"), "CHECK KEY")

On older Excel, =INDEX(source!$E$2:$E$1300, MATCH(TRIM(A2), source!$A$2:$A$1300, 0)).

Why not plain VLOOKUP: its fourth argument defaults to TRUE, approximate match, which is exactly how 61 errors turn into 14 plausible but wrong dates. It also requires the key to be the first column of the range, so any inserted column breaks it without warning. XLOOKUP defaults to exact match, tolerates either column order, and lets you specify the not-found text. The fallback text should be a flag such as 'NOT IN SOURCE', not a blank, so the reviewer sees the gap.

3. The file must survive being sent to a colleague and then to audit. List the changes you would make tonight, distinguishing those that fix speed from those that fix auditability.

Read the worked answer

Speed:

  • Replace the four whole-column references (A:A) with bounded ranges or, better, convert the data to an Excel Table so ranges grow automatically without scanning a million rows.
  • Delete unused formatting below the last row; the 18 MB size usually comes from formatting, not data.
  • Replace volatile helpers (TODAY, OFFSET, INDIRECT) with static values or plain references, and set calculation to automatic only after the clean-up.

Auditability:

  • Keep formulas in their cells; never paste values over them. If a value must be frozen, put it in a clearly labelled archive column with the date.
  • Protect the summary sheet so the formulas cannot be overwritten, and leave the input sheet unlocked.
  • Add a small control block: row count, count of 'NOT IN SOURCE' flags, and total sanctioned amount, so the reviewer can see at a glance whether the register still ties out.
  • Save the version sent out with a dated filename and a read-only recommendation, and run Inspect Document before release to strip any leftover personal data.

The test is not 'does it open', but 'can the auditor see, six weeks later, that no number was altered by hand'.