Fix an Excel or Google Sheets Formula That Returns an Error or Wrong Result

Get a ranked list of the likely reasons your formula fails, a quick check for each, and a corrected formula you can paste straight into your sheet.

You getDiagnosis summary, table and recommended fix and why

Prompt

Prompt
You are a spreadsheet troubleshooter who has fixed thousands of broken Excel and Google Sheets formulas.

Your job: find out why my formula returns an error or the wrong result, and give me a working fix.

Before answering, ask me these questions in one message, then wait for my reply:
1. Are you using Excel (which version) or Google Sheets, and does your locale use commas or semicolons between arguments?
2. Paste the exact formula and the cell it sits in.
3. What does it show (error code or wrong value), and what did you expect?
4. Paste 5-10 rows of sample data with column letters and headers.

Rules:
- Rank likely causes from most to least probable, with one line on why each fits the evidence I gave.
- For every cause, give a complete corrected formula using my real cell references, ready to paste.
- Give one quick check per cause (for example =ISTEXT(B2)) so I can confirm it before changing anything.
- Look for hidden data problems: numbers stored as text, trailing spaces, mixed date formats, merged cells, wrong ranges.
- Use only functions available in my version.

Output format:
1. Diagnosis summary (2 sentences)
2. Table: Rank | Likely cause | Quick check | Corrected formula
3. Recommended fix and why
4. How to prevent this next time (2-3 bullets)

End with one line naming any assumption about my data that I should verify.

Example output

At a glanceRanked causes of the VLOOKUP #N/A errorEach cause comes with a quick check and a corrected formula
  1. SKUs stored as text vs numbersRank 1ISTEXT(A2) TRUE; wrap lookup in VALUE()
  2. Leading or trailing spacesRank 2LEN(A2) returns 6; add TRIM()
  3. Non-breaking spaces from web exportRank 3CODE(RIGHT(A2,1)) returns 160; SUBSTITUTE CHAR(160)
  4. SKU missing from ProductsRank 4COUNTIF returns 0; wrap in IFERROR
  5. Recommended fixD2:D40XLOOKUP(VALUE(TRIM(A2)),...,"Not in product list")

Example for: Excel 365 user whose VLOOKUP in an orders sheet returns #N/A even though the SKU is visible on the Products tab.

1. Diagnosis summary

Your formula =VLOOKUP(A2,Products!A:C,3,FALSE) in Orders!D2 is structurally correct, so the #N/A almost certainly comes from the lookup values not matching exactly. In your sample, SKUs on Orders are left-aligned while SKUs on Products are right-aligned, which points to text-versus-number mismatch.

2. Likely causes

RankLikely causeQuick checkCorrected formula
1Orders SKUs (e.g. 10452) are stored as text; Products SKUs are numbers=ISTEXT(A2) returns TRUE and =ISNUMBER(Products!A2) returns TRUE=VLOOKUP(VALUE(A2),Products!A:C,3,FALSE)
2Trailing or leading spaces in Orders column A (common in CSV imports)=LEN(A2) returns 6 for a 5-digit SKU=VLOOKUP(VALUE(TRIM(A2)),Products!A:C,3,FALSE)
3Non-breaking spaces copied from a web export=CODE(RIGHT(A2,1)) returns 160=VLOOKUP(VALUE(SUBSTITUTE(A2,CHAR(160),"")),Products!A:C,3,FALSE)
4SKU genuinely missing from Products (e.g. new item 10988)=COUNTIF(Products!A:A,VALUE(A2)) returns 0=IFERROR(VLOOKUP(VALUE(A2),Products!A:C,3,FALSE),"Not in product list")

3. Recommended fix and why

Run the rank 1 and rank 2 checks on A2 first. If either is TRUE, use this in D2 and fill down to D40:

=XLOOKUP(VALUE(TRIM(A2)),Products!A:A,Products!C:C,"Not in product list")

XLOOKUP is available in Excel 365, handles the missing-item case without a separate IFERROR, and will not break if someone inserts a column between A and C on the Products tab.

4. How to prevent this next time

  • After importing order data, select column A, use Data > Text to Columns > Finish to convert text numbers to real numbers.
  • Format both SKU columns the same way (Number, no decimals) before building lookups.
  • Add a check column with =COUNTIF(Products!A:A,A2) so new SKUs show up as 0 right away.

Assumption to verify: I assumed all SKUs are purely numeric. If any contain letters (e.g. 10452-B), drop VALUE() and use TRIM() only.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for version, separators and real sample data stops the model from suggesting functions or syntax your sheet can't use.
  • Ranking causes by probability with a quick check for each lets you confirm the problem before rewriting anything.
  • Requiring corrected formulas with your own cell references means every fix is ready to paste, not a generic example.

Use when a formula shows #N/A, #VALUE!, #REF! or a wrong number. Have the formula, the error, and a few rows of real data ready to paste into ChatGPT, Claude or Gemini.

Common edits

  • Ask for a plain-English explanation of each part of the corrected formula if you're still learning.
  • Request a version that avoids newer functions like XLOOKUP so it works for coworkers on older Excel.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets