Organize information

Diagnose and fix an Excel formula error

Find the root cause of an Excel error before replacing the formula

5 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Error and formulaReferenced dataIntended behavior

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Diagnose this Excel formula problem:
[error]

Referenced data and representative values:
[context]

Intended behavior:
[intent]

Do not immediately wrap the formula in IFERROR. First list the most likely causes in evidence order and give a quick test for each. Then provide the smallest justified correction, explain exactly what changed, and show tests for a normal row, the reported failure, a blank, and a no-match case. Keep genuine data-quality problems visible instead of hiding them with a fallback value.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Error and formula: E2 shows #N/A. Formula: =XLOOKUP(A2,Inventory!A:A,Inventory!C:C)
Referenced data: A2 visually contains SKU-1042. Inventory column A contains imported SKU codes; the matching row appears as 'SKU-1042 ' with a trailing space. Inventory column C contains stock quantity numbers.
Intended behavior: Return the stock quantity for a matching SKU. Keep truly unknown SKUs visible as 'Not found'. Blank input rows should stay blank.

Example output

Most likely cause: The lookup value and imported key are not exactly equal because the inventory key has a trailing space. Confirm with =LEN(A2) and =LEN(Inventory!A2), or compare =A2=Inventory!A2. A second possibility is another non-printing character; test with CLEAN after TRIM.

Smallest justified correction: Clean the source key in a helper column, for example Inventory D2: =TRIM(CLEAN(A2)), fill it down, then use: =IF(A2="","",XLOOKUP(TRIM(CLEAN(A2)),Inventory!D:D,Inventory!C:C,"Not found"))

This preserves a visible no-match result, keeps blank request rows blank, and avoids recalculating transformations across a full imported column inside every lookup.

Tests: SKU-1042 with a cleaned source key returns its stated quantity; the original trailing-space row now matches; blank A2 returns blank; SKU-9999 returns “Not found.” Inspect duplicate cleaned keys before filling down because cleaning can collapse two different raw values into the same key.

Why this works

1
Cause-first diagnosis avoids hiding broken references or dirty data behind IFERROR.
2
A minimal correction is easier to review than an unrelated replacement formula.