Beneficiary register failing on the day a bank list is due

Diagnosis: the fourth argument (range_lookup) was omitted. Excel then assumes TRUE - an approximate match - which requires the first column of the lookup range to be sorted ascending and returns the nearest match at or below the lookup value. With unsorted account numbers, roughly 300 rows therefore pick up a neighbour's record. The account numbers being 'correct' is consistent with this: the lookup value is fine, the matching mode is not.

Corrected formula: =VLOOKUP(A2, Master!$A$2:$D$4872, 3, FALSE)

• FALSE (or 0) forces an exact match.
• $A$2:$D$4872 stays absolute so the fill-down does not shift the table.
• 3 returns the third column of the range, which must be the name column - verify that the range starts at column A, or the index will be off by one.

Two checks before filling down:

1. Data types must agree - if column A is stored as text and Master column A as numbers, FALSE will return #N/A for every row; convert both to text or both to number.
2. Wrap it: =IFERROR(VLOOKUP(A2,Master!$A$2:$D$4872,3,FALSE),"NOT FOUND") so genuine misses are visible instead of silent.

Recalculate and count the NOT FOUND rows before sending; they are the real exceptions to chase.

Cause: the cells are formatted as a number. Excel stores numeric values with limited displayed precision, so a leading zero is not a value and is dropped, and a long number in a narrow column (or a value over 11 digits) is rendered in scientific notation - 9.87E+09. Re-saving as CSV makes it worse, because CSV keeps only the stored value, not the cell format.

Fix 1 - format before entry (best for new data): select the column, Format Cells → Text, then type or paste the numbers. Anything entered is stored as a character string, zeros and all.

Fix 2 - apostrophe prefix: type ' before the number (e.g. '01234567890). Excel stores it as text and hides the apostrophe; this works for a handful of cells but is error-prone at 4,872 rows.

Fix 3 - import rather than open (best for existing data): bring the source file in through the Text Import Wizard / Power Query and set that column's type to Text at import. Data → From Text/CSV lets you choose the type before the data ever lands in the sheet.

Verification: after fixing, check length with =LEN(B2) - it must return 11 for every account - and spot-check the zero-prefixed ones against the passbook. Never re-open and re-save the fixed sheet as .csv.

#REF! means the referenced cells no longer exist. Deleting the column removed the cells the 'Age' formulas pointed at, so the formulas cannot be repaired by editing the reference - the reference target is gone.

Fastest repair, in order:

1. Ctrl+Z / Undo if the file has not been closed since the deletion - this restores the column and the formulas together.
2. If already saved, use File → Info → Version History / AutoRecover, or yesterday's backup copy, and re-apply only the changes made since.
3. If neither exists, re-enter the Age formula against the surviving date-of-birth column, then re-add the deleted column as a new column (it will not carry the old references back) and copy the values from the backup.

Prevention to note in the file: insert columns and hide them instead of deleting; keep a dated backup before structural edits.

Email format: send .xlsx (or .ods if LibreOffice is the working tool). It preserves formulas, text formatting and the leading zeros. Send .csv only if the recipient's system demands it, and in that case export a copy in which the account column is pre-formatted as text, then re-open the exported file yourself and confirm 11 digits survive before attaching. Never attach a sheet that still shows #REF! or E+09 - a wrong bank list is worse than a late one.