The #N/A Fee Register
At a coaching centre in Guwahati, the owner maintains one workbook for 412 fee receipts. The 'Fees' sheet has Receipt No, Student ID, Name, Course, Amount and Date; the 'Students' sheet has Student ID, Name, Course and Phone. A lookup in column G returns #N/A for 37 receipts. On inspection, some Student IDs in 'Students' are stored as text with leading zeros (for example, 00127), while the same IDs in 'Fees' are numbers (127); several IDs have trailing spaces; six Receipt No values appear twice with different dates. The owner wants a course-wise total for April–June and has already typed a few totals manually. She proposes replacing all #N/A with 0 and merging the two sheets into one so 'it stays simple'. The April–June parent meeting is in three days, and she asks you, the centre's part-time IT assistant, to fix the workbook before it.
1. Identify the most likely causes of the #N/A errors and give the order in which you would clean the data before recalculating. Justify why replacing #N/A with 0 is unsafe.
Read the worked answer
The errors almost certainly come from key mismatch, not from a broken lookup formula. Two different data types (text '00127' versus numeric 127) will not match, and trailing spaces make visually identical IDs unequal. Duplicate Receipt No values also mean a lookup can return the first match and silently misstate a total. I would work in this order: (1) save a copy and record the current control total; (2) remove or flag duplicate receipts using a documented rule, for example keep the earliest dated receipt unless finance confirms otherwise; (3) create a helper column that standardises Student ID to one type, using TRIM and CLEAN, then convert to fixed-width text; (4) validate every fee ID against the student master and list unmatched IDs for human correction; (5) only then rebuild the lookup and totals. Replacing #N/A with 0 would hide missing students, understate fees and destroy the audit trail. It also makes the manual totals impossible to reconcile, because a zero looks like a valid paid amount.
2. Recommend a method for the April–June course-wise total and explain how you would verify it against the owner's manual totals.
Read the worked answer
Because one student can have many receipts, keep the sheets separate and add a Course column to the cleaned Fees table with a lookup on the standardised Student ID. Then use SUMIFS, not a whole-column VLOOKUP: =SUMIFS(Fees!$E:$E, Fees!$D:$D, $A2, Fees!$F:$F, ">="&DATE(2025,4,1), Fees!$F:$F, "<="&DATE(2025,6,30)), where D is Course and F is Date. Alternatively, build a pivot table from the cleaned table. Verification should be three-way: (a) the sum of all course totals must equal the control total of deduplicated April–June receipts; (b) the count of receipts in the pivot must equal the deduplicated row count; (c) pick five receipts, including one previously #N/A, and trace each to the source. Investigate any difference above ₹1 rather than plugging it. The manual totals are useful only as a cross-check, not as the source of truth.
3. The owner still wants to merge the two sheets. Give a reasoned response and one compromise that protects data quality.
Read the worked answer
Merging would mix two different grains of data: one row per student and one row per receipt. Every receipt would repeat the student's name, course and phone, so a phone correction would have to be made in many rows and would eventually disagree. Deleting a student's last receipt would also delete the only record of that student, and duplicate names would make matching ambiguous. I would explain this with a simple example: a student with three receipts would appear three times, so a course-wise total could double-count if the merge is done carelessly. The compromise is to keep the master and transaction sheets separate, but give the owner a protected 'Dashboard' sheet with course and month totals, data validation on Student ID, and a formatted pivot. If she wants a single view, use Power Query or a linked table to combine them for reporting without overwriting the sources.