VLOOKUP Failures, a Lost Leading Zero and a Mail Merge That Will Not Merge
On 20 June you are asked to prepare a ward-wise directory of 14 sanitation supervisors for the health department on a spreadsheet, and then to send each supervisor a personalised reminder letter by mail merge. You build the sheet with column A Employee ID, B Name, C Mobile number, D Landline, E Ward. Three problems appear. First, a VLOOKUP of Employee IDs against the master list returns #N/A for exactly three supervisors, although you can read the same IDs in both sheets. Second, the mobile column displays values such as 9.87654E+09, and the landline 079-23456789 has become 7923456789. Third, when you run the mail merge, the letter body shows the code «Ward_Name» as plain text for every record instead of the ward name. The master list, which you did not create, has a merged title row across A1:E1 and a blank row 2. The finished file is to be emailed to the health officer the same afternoon.
1. Diagnose the three problems and give the exact fix for each, in the order you would apply them.
Read the worked answer
1. Fix the sheet structure before anything else. The merged title row and the blank row 2 mean the header row is not a clean single row. Sorting, filtering, VLOOKUP and mail merge all read the header from row 1 by default. Unmerge A1:E1, delete the blank row 2, put field names in a single header row with no spaces, then re-apply.
2. The #N/A for three IDs is a matching problem, not a missing-data problem. The IDs look identical on screen but differ as data: usually a trailing or leading space, a non-breaking space pasted from a web page, or one sheet storing the ID as text and the other as a number. Diagnose with =LEN() on both sides and check for a discrepancy; fix with TRIM/CLEAN, or Find & Replace a space with nothing, or align types with VALUE()/TEXT(). Then confirm the lookup uses an exact match (fourth argument FALSE or 0) - a TRUE/omitted argument will also produce nonsense on unsorted data.
3. The mobile column is a display problem only. Excel holds 15 significant digits, so a 10-digit mobile number is not corrupted; it is merely shown in scientific notation because the column is too narrow or formatted as General. Fix: format the column as Text (or set the column width), or use a custom format of 0. If the column is to be exported to CSV, Text is safer so nothing is re-interpreted downstream.
4. The landline is a real data-loss problem. Entered as a number, 079-23456789 becomes 7923456789: the leading zero and the hyphen disappear permanently. Re-enter as text with an apostrophe or a Text-formatted column. This one cannot be fixed by formatting; the original numbers must be recovered from the source list.
5. «Ward_Name» appears as literal text because it is not a merge field in the data source. The header is probably 'Ward' or has a space in it. Rename the header to a single word, then re-select recipients in the mail merge so the field list refreshes.
2. Which of the three problems is a data-quality problem rather than a display problem, and why does the distinction matter before you email the file?
Read the worked answer
The landline is the data-quality problem. The leading zero is gone from the stored value; no amount of column widening or reformatting brings it back. The mobile column is purely presentational - the ten digits are intact and only the display is unhelpful. The three #N/A results are a matching/consistency problem that is recoverable by cleaning, but until it is fixed the directory is silently incomplete.
Why it matters before sending.
- A display problem fixed by formatting is safe to send once fixed.
- A matching problem means the sheet is wrong - three supervisors are effectively missing, and a VLOOKUP that failed quietly also means any dependent formula (counts, ward totals) is understated. Sending it means the health officer's ward counts are wrong.
- A data-quality problem means the source must be re-read. If the file goes out with landlines missing their leading zero, the department will dial wrong numbers and the error will be attributed to your office, not to the formatting.
Practical rule: before emailing, never rely on how the sheet looks. Check a sample of values in the formula bar, check that every row matches the master count (14 supervisors must return 14 matches; count the #N/A cells explicitly), and send the file only when the count reconciles. Fixing three #N/A rows is ten minutes; retracting a circular sent to fourteen supervisors is not.
3. The health officer asks you to keep everything in one sheet, with the merged title across the top, because it 'looks like an office letterhead' when printed. What do you advise?
Read the worked answer
Advise two sheets, one for data and one for print. The data sheet carries a single clean header row, no merged cells, no blank rows, one record per row, and one field per column. A second sheet, or the page header facility, carries the letterhead: corporation name, department, title, date and page numbers.
The reasoning to give the officer
- Merged cells and blank rows break sorting, filtering, subtotals and pivot tables. If the officer sorts by ward tomorrow, the layout will scatter values.
- They also break lookups and mail merges, which is exactly what has already happened in this file.
- Print presentation can be reproduced exactly without touching the data: Page Layout > Print Titles for a repeating header, and the built-in header/footer for the letterhead. The printed result is identical; the underlying data stays usable.
- If a single file is mandatory, keep the letterhead on the second sheet and print both - the officer sees one file and one print command.
The general point worth making once, politely, at the counter: a spreadsheet is a database that happens to print, not a letter that happens to hold numbers. Design it for the data, and dress it for the printer separately.