Library › Coding AI › Office and admin

Write a rep email lookup that works in Excel 2016

Practice: Giving the right context. About 5 minutes.

About this exercise

You need a formula for Orders!E2 (filled down) that pulls each rep's email by the rep name in Orders column C. The staff list is on a tab named Staff 2026 (with a space), rows 2-40. Your laptops run Excel 2016, so no XLOOKUP, and the shop system adds a trailing space to every rep name, so trim C2. If a name isn't found, show CHECK NAME. Give one formula in one code block, then one line; under 25 words outside the code, no alternatives.

Common mistakes when you ask AI for this

  • Not saying your Excel version. The AI reaches for XLOOKUP, which shows #NAME? in Excel 2016. Try: Add: "Excel 2016, no XLOOKUP."
  • Not naming the tab. The AI guesses a tab name like Staff, so the formula points at nothing. Try: Add: "The tab is named Staff 2026, with a space."
  • Hiding the messy data. The AI can't see trailing spaces, so every match fails. Try: Add: "Names in column C end with a space; trim them."
  • Leaving the not-found text open. The AI shows #N/A or its own wording, not the text your team looks for. Try: Add: "If not found, show CHECK NAME."
  • Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 15 words. Nothing else."

What a strong prompt includes

  • Names the Excel version
  • Names the staff tab
  • Mentions the trailing space
  • Sets the not-found text
  • Asks for one answer only

Practice this now ↑

Related exercises