Find why VLOOKUP returns #N/A with AI, then prove it in cells

Three rows out of 20 return #N/A. The order codes are plainly in the price list. Asking an AI "why is my VLOOKUP broken" gets you a generic list of causes. Give it the formula, a few rows and their cell formats, and ask for formulas you can type into cells to test each guess.

[1] Gather the facts. Sheet "Orders": column A is the order code, column B the unit price, and the price list sits in E2:F21. B2 was filled down to B21. Pick a few rows, good and bad.

In the price list: E3=A-102, E5=A-104, E6=B-201, E17=1023 (a number, right-aligned). Alignment hints at number versus text. A trailing space is invisible, so you cannot include it.

[2] Write the prompt. It says the model cannot see the file, caps the causes at four in likelihood order, and asks for one test formula per cause. This follows the advice in Anthropic's prompting guide: treat the model like a new colleague with no context and spell out the format and constraints.

An Excel VLOOKUP returns #N/A. Diagnose using only the information inside <sheet>, and assume you cannot see the file. Rules:- List at most 4 possible causes, most likely first. For each, point to the rows it could explain using the information shown; if you cannot tell, write "needs checking".- For each cause give one formula to type in an empty cell to test it (English function names, for row 2, fillable downward), plus one line on which result confirms the cause.- You have not seen the data, so separate guesses from facts. Do not state anything as confirmed.- End with one formula that counts the remaining errors by counting error values directly, not with COUNTIF.- No other text. <sheet>{formula · sheet layout · per-row shown value / format / alignment / result · price list excerpt}</sheet>

[3] Paste [1] into the braces and run it. Explanatory text is trimmed from the first English run.

**1. Unanchored lookup range (guess, likely)**- Fact from the formula: `E2:F21` has no `$`, so in row N it becomes `E(N):F(N+19)`. By row 10 the range starts at E10.- Row 10: A-104 is in E5, which is outside E10:F29. This could explain it. …- Row 4: E6 is inside E4:F23, so this cause does not explain it. Rows 13 and 16 are also not explained.- Test: `=ISNUMBER(MATCH(A2,$E$2:$E$21,0))`- Confirms: TRUE in a row where column B shows #N/A (expected for row 10). **2. Text code vs numeric code (guess, likely)**- Fact from the sheet: A13 is formatted Text and left-aligned, while E17 (1023) is a right-aligned number.- This could explain row 13. The range E13:F32 includes E17, so drift does not. …- Test: `=IFERROR(VLOOKUP(VALUE(A2),$E$2:$F$21,2,FALSE),"no match")`- Confirms: row 13 returns a price instead of #N/A. Text codes like A-102 will show "no match" because VALUE fails on them, which is expected. **3. Extra spaces or non-printing characters in A or E (guess)**- Row 4: needs checking. B-201 is in E6, inside the drifted range, and nothing shown explains the error.- Rows 10 and 13: possible, but needs checking.- Test: `=LEN(A2)-LEN(TRIM(CLEAN(A2)))`- Confirms: any value above 0 in a #N/A row means A has extra characters. A result of 0 does not clear the price-list side. Check E6 the same way.…**Remaining errors:** `=SUMPRODUCT(--ISERROR(B2:B21))`

Rows 10 and 13 got a confident match: drifted range and text versus number. Row 4 stayed "needs checking" under every cause, with hidden characters as the leading guess.

[4] Type the AI's formulas into the sheet. Put =LEN(A2)-LEN(TRIM(CLEAN(A2))) in H2 and =ISNUMBER(MATCH(A2,$E$2:$E$21,0)) in I2, and fill down. The expected values below come from a script that models Excel's rules, not from Excel itself.

A row with I = TRUE and B = #N/A (row 10) is a range problem: click B10 and the formula reads E10:F29. For row 13, =ISNUMBER(A13) returns FALSE and =ISNUMBER(E17) returns TRUE.

[5] Fix all three.

• Row 4: strip the trailing space. The formula below trims text cells. If the cell holds a non-breaking space (CHAR(160)), TRIM and CLEAN skip it; SUBSTITUTE it first.
• Row 13: convert the cell to a number. If the price list holds text, match the other way.
• Range: change B2 to =VLOOKUP(IF(ISNUMBER(A2),A2,TRIM(A2)),$E$2:$F$21,2,FALSE) and fill down again. The $ signs pin the range. Do not use a plain TRIM(A2) here: TRIM always returns text, so after row 13 is a number, numeric codes such as 1025 would turn into text and miss the numeric keys in the price list. IF(ISNUMBER(…)) leaves numbers alone and trims only text.

I calculated this on the sample with the Python formulas package, which evaluates the real Excel formulas (still not Excel itself). The original B2 gave 3 #N/A (rows 4, 10, 13). With TRIM(A2) alone, row 13 already converted to a number, it gave 3 #N/A again (rows 13, 16, 17, all numeric codes). The formula above gave 0.

[6] Count what is left. The AI offered =SUMPRODUCT(--ISERROR(B2:B21)), but ISERROR also counts #VALUE! and #REF!. To count only #N/A:

=SUMPRODUCT(--ISNA(B2:B21))

It reads 3 before the fixes and should read 0 after (3 → 0 in the same calculation, with the formula from [5]). Anything else means a new cause: rerun [2] with the remaining rows.

Where it goes wrong

• The AI could only guess at the trailing-space row. What cannot be seen on screen has to be proved with the LEN difference in [4].
• The test formulas are the AI's own. Try each formula on a row you already know the answer for, as in [4], before trusting it.
• The model never committed on row 4: every cause said "needs checking", and the look-alike-character test (an en dash, the letter O for zero) cannot see the trailing space that is actually there. Only the LEN difference in [4] shows it.
• A price list with its own stray spaces was not in the sample, so it was not tested.
• Swap real customer names and prices for made-up values before pasting.