Prompt Almanac

Audit intermediate Works with: Any AI

Lookup and Match Troubleshooter

Decision tree Has example

Diagnose VLOOKUP, XLOOKUP, INDEX/MATCH failures.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Rate this prompt

Loading ratings…

Diagnose why my lookup formula returns the wrong result or an error. Spreadsheet app and version: [SPREADSHEET_APP]. The formula: [PASTE_FORMULA]. What I expected and what I got: [EXPECTED_AND_ACTUAL]. Small samples of the lookup value and the lookup range (copy several cells, including some that fail): [SAMPLE_DATA].

Follow this decision tree and show which node explains my problem:
NODE 1: Is the result #N/A? Check for extra spaces (use TRIM), non-breaking spaces, different capitalization (matters in some functions), numbers stored as text versus real numbers, and hidden characters (use LEN to compare lengths).
NODE 2: Is the result wrong but not an error? Check approximate match versus exact match setting, unsorted data with approximate match, duplicates in the lookup column (first match wins), and the wrong column index number after inserting columns.
NODE 3: Is the range wrong? Check for absolute and relative references (dollar signs), ranges that stop short, and merged cells.
NODE 4: Is a calculation recalculating? Check manual calculation mode and circular references.
NODE 5: Is the lookup column on the left? If using an older function, suggest a more flexible replacement.

Give the corrected formula, a test that proves it, and a helper column formula to detect mismatches. Explain the cause in two sentences.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

See an example result (fictional data)
Formula: =VLOOKUP(A2, Prices!A:C, 3, FALSE) returns #N/A for product "AB-104".
Node 1 applies: the lookup value has a trailing space ("AB-104 "); LEN shows 7 versus 6 in the table. Fix: =VLOOKUP(TRIM(A2), Prices!A:C, 3, FALSE).
Node 2 also: some prices return the wrong column after you inserted a column; use XLOOKUP so the return range moves with the table: =XLOOKUP(TRIM(A2), Prices!A:A, Prices!C:C, "not found").
Helper test (column D): =ISNUMBER(MATCH(TRIM(A2), Prices!A:A, 0)) to flag mismatches.
Cause in two sentences: the codes had hidden spaces from the export, and the column index shifted after a column insert.

Illustrative only, made-up data. Your results will differ and must be reviewed.

Related prompts