Excel Study · Module 6: Lookups
Class 49Why VLOOKUP says #N/A
Intermediate2:17 videoFree
VLOOKUP shows #N/A when it cannot find what you asked for. Four everyday reasons, each made and fixed in Excel: a table range that slid when filled down, a number stored as text, a value not in the first column, and an extra space (TRIM).
Try this AI prompt
Works in ChatGPT, Gemini, Copilot or Claude.
In Excel, B2:B7 has staff names and C2:C7 their branches. D5 has a name that may have extra spaces, so =VLOOKUP(D5,$B$2:$C$7,2,FALSE) in E5 shows ＃N/A. Write a VLOOKUP formula for E5 that still finds the branch.
The answer that works
✓ checked in Excel=VLOOKUP(TRIM(D5),$B$2:$C$7,2,FALSE)
AI can get Excel wrong. Paste its answer into Excel and test it before you trust it.