The Fees Sheet That Reports Zero Collected

#N/A from VLOOKUP with a 0 (exact match) fourth argument means one thing: the lookup value was not found in the first column of the table array. With ~40 failures out of 180, the cause is almost certainly data type, not missing students.

Likely causes, in order of probability:

1. Text vs number. Roll numbers such as 007 in "Master" were entered or imported as text; retyping them in "Fees" stored them as numbers (7). Excel treats "007" and 7 as different values, so the lookup fails. It also destroys the leading zeros, and roll 007 may now collide with a genuine roll 7.
2. Trailing spaces from a copy-paste or a CSV export - "007 " never matches "007".
3. The table array is not absolute. Master!A2:C181 shifts as the formula is copied down; by row 40 it may be reading Master!A41:C220, so late rows fall outside the range and return #N/A.

Fix, in this order:

• Make the range absolute: =VLOOKUP($A2,Master!$A$2:$C$181,2,0).
• Standardise the key. Best long-term: keep the roll number as text in both sheets, formatted 000, enforced with Data > Data Validation and a text input message. A working formula that forces the format is =VLOOKUP(TEXT($A2,"000"),Master!$A$2:$C$181,2,0), and =TRIM(...) around the key handles stray spaces.
• Fast clean-up of a whole column: Data > Text to Columns > Finish (no delimiter change needed) re-coerces values; or Paste Special > Multiply by 1 to turn text numbers into real numbers.
• Wrap for presentation sanity: =IFERROR(VLOOKUP(...),"Not on master list"), and count the misses with =SUMPRODUCT(--ISNA(G2:G181)) so she reports 40 exceptions rather than silently showing #N/A to the owner.
• A 4th-argument mistake is not the cause here but worth stating: omitting the 0 (approximate match) on unsorted data returns wrong phone numbers with no error at all, which is worse than #N/A.

Why SUM gives 0. SUM ignores text. The amounts are left-aligned in the column, which is Excel's default for text, so D2:D181 holds strings such as "12,000", not numbers. Confirm in three seconds: =ISTEXT(D2) returns TRUE, and =COUNT(D2:D181) returns 0 while =COUNTA(D2:D181) returns 180. Nothing is lost - it is a type problem.

Repair: select D2:D181, then Data > Text to Columns > Finish (fastest, handles comma thousands separators), or type 1 in a blank cell, copy it, and Paste Special > Multiply over the range. Then format as Number/Currency, right-aligned, and re-check =SUM(D2:D181) against a hand-total of three rows. Watch the locale trap: if VALUE() or Text to Columns errors on 12,000, strip the comma first with =SUBSTITUTE(D2,",","") before converting.

What #### means. The column is too narrow for the number (or the cell holds a negative date/time). It is a display overflow, not corrupted data and not an error value. Widen the column, or set a sensible date format. Do not go looking for the "missing" payment date.

Before quoting any total, check:

• Duplicate roll numbers - a duplicate inflates the total; =SUMPRODUCT(--(COUNTIF(A2:A181,A2:A181)>1)) counts them.
• Hidden rows or an AutoFilter that is still on. SUM totals hidden rows too, so the on-screen filter view and the grand total will disagree; use =SUBTOTAL(109,D2:D181) for a visible-rows figure, and say which figure you are quoting.
• Whether "Fee Paid" is cumulative or per-instalment. If students pay in instalments, summing D gives collections, not outstanding dues - the dues live in column E and must be reconciled against the fee schedule.
• Recalculate after all conversions (F9) and save the cleaned workbook before building slides from it.

The owner's question is "which batch still owes the most?" - every slide should serve that, not the 180 rows.

Deck plan (one message per slide):

1. Title + headline number: total collected, total outstanding, as on date.
2. Outstanding dues by batch - the direct answer, sorted descending.
3. Top 10 defaulters with roll, name, batch and amount.
4. Collection trend by month (or by instalment due date), to show whether the gap is widening.
5. Two or three named actions (reminder calls to the top 10, weekend batch follow-up).

Chart decisions:

• Build each chart from a small summary table made with SUMIFS (or a pivot table) over the cleaned sheet - never chart the raw 180-row range.
• A pie chart is wrong here: it only works for parts of one whole with a handful of slices. Use a sorted bar chart for dues by batch and a column chart for the monthly trend; drop 3-D effects and gridlines, add data labels in ₹ thousands, and make sure the chart reads from the back of the room and does not depend on colour alone.

Paste decisions in PowerPoint:

• If the numbers are final: Paste Special > Picture - cannot be edited by accident, small file, renders identically on any projector.
• If she may re-check figures the morning of the meeting: Paste with Keep Source Data (entire workbook) and Refresh Data, or Paste Link. Both carry a real risk: the link breaks if the workbook is moved or renamed, and the embedded workbook may carry the full member list into a file she emails around. Pasted pictures avoid that data-leak risk entirely.
• Add speaker notes with the exact figures, and carry a PDF of the slides as a fallback - the projector failing should not mean the owner does not get the numbers.