Audit spreadsheet lookup results before sharing a report

Write the lookup contract before touching the formula

Preserve an untouched copy of the workbook. Work in a separate checking copy and record the source export’s filename and reporting cutoff. Write down what one report row represents and which source version should supply its customer name. A November customer list may be inappropriate for an October report if account ownership changed.

For this exercise, create a sheet named Customers with headers in A1:B1: Customer ID and Customer name. Enter these fictional records in rows 2–5, in the order shown. On a sheet named Report, put Customer ID in A1 and the four report identifiers below it.

The contract is one current customer record per ID. C-208 deliberately violates it; C-999 deliberately has no source record. Neither should receive an unqualified customer name. In a real workbook, substitute verified first and last data rows for this exercise’s short ranges.

Expose what the existing formula is allowed to find

Inspect the first, middle, and last populated lookup cells in the formula bar. Record the lookup cell, source boundaries, return field, and any error replacement. Check for a typed value where a formula should be. Compare the boundaries with the actual source, including newly appended records.

For identity matching, require exact matches. Microsoft’s XLOOKUP reference defines exact matching as mode 0, forward searching as mode 1, and warns that binary searches require correctly sorted data. An audit formula for Report!B2 is:

=XLOOKUP(A2,Customers!$A$2:$A$5,Customers!$B$2:$B$5,"MISSING ID",0,1)

Fill B2 down through B5. Expect Alder Studio, Birch Services, MISSING ID, and Cedar Works. These are predictions from the fictional inputs. The plausible Birch Services result is precisely the trap: XLOOKUP returns the first match, so a duplicate can remain invisible.

For an existing VLOOKUP, Microsoft documents that omitting its final argument selects approximate matching. Inspect it explicitly. The equivalent exact formula here is =VLOOKUP(A2,Customers!$A$2:$B$5,2,FALSE). Its column number is relative to the selected range; verify that column 2 still means Customer name. See the VLOOKUP documentation. XLOOKUP is unavailable in Excel 2016 and 2019; retain a compatible exact lookup when those versions must calculate the workbook.

Keep the lookup and return ranges aligned on the same source rows. Equal-length ranges offset by one row can attach another customer’s name without an obvious error. Do not sort only the identifier column: that separates keys from their records.

Count candidates before accepting a returned name

Add Candidate count in Report!C1. In C2 enter the following formula, then fill through C5:

=IF(A2="","BLANK KEY",COUNTIF(Customers!$A$2:$A$5,A2))

Expected counts are 1, 2, 0, and 1. One means a candidate exists uniquely within the checked range; zero means unresolved; more than one means ambiguous. The blank-key branch stops an empty report identifier from being mistaken for a legitimate lookup request.

Add Audit status in D1, then enter and fill:

=IF(A2="","BLANK KEY",IF(C2=0,"MISSING",IF(C2>1,"DUPLICATE","CHECK FIELD")))

CHECK FIELD deliberately avoids claiming success from uniqueness alone. Read all rows marked MISSING, DUPLICATE, or BLANK KEY, including rows outside the currently visible report view. Record each exception’s report row, identifier, suspected cause, and responsible owner.

Microsoft’s COUNTIF documentation explains that matching ignores letter case and treats asterisks and question marks as wildcards. This exercise therefore uses short IDs containing only letters, digits, and hyphens. Do not reuse the helper unchanged for identifiers containing wildcard characters, case-sensitive codes, or strings longer than 255 characters. Define the matching rules and adapt the audit before trusting its counts.

Repair the reason for an exception, preserving the original key

For C-208, ask which source record is authoritative at the reporting cutoff. Do not delete Birch Archive simply because it appears second. Two records may describe different branches or effective periods. If both legitimately belong, the lookup needs a more specific identifier or an explicit date rule; choosing the first or last match cannot establish that rule.

For C-999, compare the original transaction with the approved customer register. Correct a confirmed transcription error, obtain a corrected source export, or retain the unresolved exception. Never replace a missing customer with the nearest code. If the source is incomplete, a cleaner formula cannot supply the missing evidence.

When an apparently present key fails, compare its stored content on both sheets. Preserve raw identifiers beside any proposed correction. Keep leading zeros when they belong to the identifier; converting every code into a number can merge different accounts.

For an ordinary trailing-space problem, try =TRIM(A2) in a separate helper cell and compare it with the original. Microsoft’s TRIM reference says it also collapses repeated ordinary spaces inside text and does not remove nonbreaking spaces by itself. Treat trimming as a proposed change, not universal cleaning. After any normalization, repeat the uniqueness check: two previously distinct raw keys might now collide.

Challenge unique matches with records and deliberate failures

For each CHECK FIELD row in this exercise, read the identifier and customer name together in the source. Compare them with the report. In a larger report, inspect every exception and trace selected unique matches independently, including the first and last source rows, newly added IDs, and important accounts. Record which rows were traced; a sample does not establish correctness for every uninspected field.

In the checking copy, temporarily change one report identifier to an unused code and another to blank. The count and status helpers should visibly flag MISSING and BLANK KEY. Restore both inputs afterward. These proposed challenge checks verify that the audit can reject bad inputs; no hands-on result is claimed here.

Inspect the first and last filled helper formulas again. The report reference should advance from A2 to A5 while the dollar-marked source boundaries stay fixed. If a helper is absent, errors, or uses different boundaries from the lookup, that row has not passed the audit.

Release the report with a compact evidence note

After approved repairs, rerun the checks on the actual workbook being shared. Require no unexplained blank keys, missing matches, or duplicate candidates for required fields, plus agreement in the independent field checks. A unique match to an obsolete register still fails the source-version contract.

Retain the source version, cutoff, checked ranges, exception decisions, traced rows, and remaining limits in a short audit note. If unresolved rows must be excluded under an approved reporting rule, state the exclusion and its effect explicitly. If no such rule exists, hold the affected report.

Finally, inspect the exact file or exported report intended for recipients. Confirm that corrected names and exception disclosures survived. If a repair produces new mismatches, return to the preserved copy, isolate that change, and repeat the candidate and field checks before sharing again.