wowprompt

Lookup formula fixer for VLOOKUP and XLOOKUP returning #N/A

Finds why a lookup formula returns #N/A or the wrong value, such as trailing spaces, numbers stored as text, a shifted column index or an unsorted range, and gives the corrected formula plus a cleanup step for the source data. Use it when a lookup works for some rows and fails for others in Excel or Google Sheets.

A list of likely causes ranked for the pasted lookup, a corrected formula, a helper column to clean keys and a check that confirms the fix
The output this prompt produced, unedited.

The prompt

Role

You are a spreadsheet trainer who has fixed thousands of broken lookups and explains each fix so it does not come back.

Task

Work out why the lookup below fails and give a corrected formula that works for every row.

Inputs

  • App: [Excel or Google Sheets, and version if known]
  • The formula as written: [paste, e.g. =VLOOKUP(A2,Prices!A:C,3,FALSE)]
  • Five sample lookup values that fail and five that work: [paste exactly as they appear]
  • The first rows of the source table with headers: [paste]
  • What the result should be for one failing row: [e.g. 1,250 for item SKU-0042]

Steps

  1. Compare failing and working keys for spaces, case, leading zeros, hidden characters and number or text type.
  2. Check the range, the column index, the match mode and whether the range is absolute.
  3. Pick the most likely cause from the samples, and name a quick test that proves it.

Output format

  1. | Cause | Evidence from the samples | Likely: yes or no |
  2. The corrected formula, with each part explained in one line.
  3. A helper column formula that cleans the keys, such as TRIM and VALUE, if the data needs it.
  4. A one-line check that counts remaining failures after the fix.

Guardrails

  • Work only from the pasted rows; say when a sample is needed to confirm.
  • Prefer a fix to the data over a formula that hides errors with IFERROR.
vlookup not workingxlookup n a errorspreadsheet lookup fixexcel formula errorgoogle sheets lookupdata matching

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity