Information
Match the first error in a column: jump straight to broken rows
Identify the first record with a formula error and list all affected records using an audit helper column.
Build an audit header that names the exact broken row when totals fail
Create an error flag in D2 with =IF(ISERROR(C2),1,0), then fill it through D5. This flags actual calculation errors; text that merely says "#DIV/0!" is not an Excel error.
XLOOKUP returns the first flagged record in row order. TEXTJOIN and FILTER list every flagged ID, while SUM(D2:D5) counts flagged rows. Keep the ranges aligned and extend them when adding records.
These checks detect formula errors, not every data-quality problem. An incorrect but numeric result will not be flagged.
| OrderID (A) | Units (B) | UnitFee = 120/B (C) | ErrFlag (D) | Audit Status |
|---|---|---|---|---|
| ORD-301 | 4 | 30.00 | 0 | OK |
| ORD-302 | 0 | #DIV/0! | 1 (First!) | Flagged 1st Broken ID |
| ORD-303 | 6 | 20.00 | 0 | OK |
| ORD-304 | 0 | #DIV/0! | 1 | Listed in Full Error Log |
First broken Record ID and error-list audit formulas
Returns "All Clean" as soon as every row in the audited column evaluates without error.
=XLOOKUP(1, D2:D5, A2:A5, "All Clean")List all Record IDs currently throwing formula errors
Combines FILTER on ErrFlag = 1 with TEXTJOIN.
=TEXTJOIN(", ", TRUE, FILTER(A2:A5, D2:D5 = 1, "All Clean"))Try the example
Use the original values or change them to test your understanding.