Merging 4,800 Beneficiary Rows Before a 5 p.m. Cutoff
A block office must submit a consolidated Excel list of 4,800 beneficiaries before a 5 p.m. cutoff. The workbook is assembled from 12 village files. Column A holds the beneficiary ID. In about 300 rows the ID was typed as text - some carry a leading apostrophe, some were entered in the form RJ-0012 - while the master sheet stores plain numbers. Column B names carry trailing spaces picked up on copy-paste. About 15 names appear twice because one village sent its sheet in twice. The operator plans to use VLOOKUP against the master sheet to pull the bank account number into column D, then sort the whole sheet by block name and print 96 pages for signature. A colleague suggests that the quickest route is to retype the 300 IDs by hand. The operator has Excel 2016 on Windows 10, no internet, and 90 minutes left.
1. Why does VLOOKUP return #N/A for those 300 rows when the IDs 'look the same'? Which fix is safest given the deadline, and why is retyping a poor choice?
Read the worked answer
VLOOKUP with an exact-match argument compares value and data type. A number 12 and the text string 12 are not equal to Excel, and RJ-0012 is text that can never equal a number at all. Trailing spaces in names cause the same failure in name-based lookups.
The non-destructive fix is a helper column that normalises the key once and is then used by every lookup, for example: strip the prefix with SUBSTITUTE(TRIM(A2),"RJ-",""), remove stray spaces (including CHAR(160) non-breaking spaces from web-pasted data), then wrap in VALUE(...) so the result is a true number. Look up on the helper column, paste the results back as values, and keep the helper column as an audit trail.
Retyping 300 IDs by hand is the worst option here, for three reasons: it consumes most of the 90 minutes; it introduces fresh keying errors into exactly the field that must reconcile to the master; and it destroys the ability to prove afterwards that the IDs were not altered. A formula is repeatable and reviewable; a retype is neither.
2. The operator writes `=VLOOKUP(A2,Master!A2:D5000,4,0)` in D2 and drags it down 4,800 rows. Explain what goes wrong, then explain what breaks if the sheet is sorted by block name after the paste.
Read the worked answer
The reference problem. Master!A2:D5000 is a relative reference. As the formula is dragged down, row 3 looks at Master!A3:D5001, row 4 at A4:D5002, and so on. By row 4,800 the lookup table has slid 4,798 rows off the real data, so late rows return wrong account numbers or #N/A - and because the shift is gradual, the first few hundred rows look perfectly correct. The range must be anchored as Master!$A$2:$D$5000 before copying.
The sorting problem. Sorting moves rows, and any formula whose references are relative to its own row is re-pointed by Excel. If column D still contains live VLOOKUP formulas, sorting by block name makes each formula look up its neighbour's ID, so account numbers silently attach to the wrong people. The order of operations must be: compute, Copy → Paste Special → Values on the result column, verify, and only then sort.
Both faults share a property the exam likes: the file looks correct on screen, and the error is only visible in the rows nobody re-checks.
3. Design a 10-minute verification step that would catch the 15 duplicate rows and any unmatched IDs before the 5 p.m. submission.
Read the worked answer
Verification has to answer three separate questions, so use three separate checks rather than one glance at the sheet.
- Row count.
COUNTAof the ID column should equal 4,800 after removing the 15 duplicates. If the count is 4,800 with the duplicates still in, 15 genuine beneficiaries have been lost somewhere in the merge. - Duplicates.
=COUNTIF($A$2:$A$4801,A2)in a helper column; anything above 1 is a repeated ID. Do not delete on sight - confirm which copy carries the correct bank details, because one village may have sent a corrected sheet. - Lookup completeness.
=COUNTIF(D$2:D$4801,"#N/A")should be zero. A residue of unmatched IDs means the normalisation in the helper column did not cover every format, and those records will reach the bank with no account number.
Finish by spot-checking five random rows against the source file by hand, and save a copy under a dated filename before printing. The spot check catches what the formulas cannot: a correct ID attached to the wrong person's name.