PhraseryPhrasery homeGet Phrasery
Data & SQL21 categories

Build and explain a spreadsheet formula

When the lookup keeps returning N/A and the nesting is four deep.

Longspreadsheetsformulasdebugging
Help me with a spreadsheet formula.

If I am asking you to write one:
1. State your assumptions about the layout: which ranges hold what, whether headers are included, whether the data grows.
2. Give the formula, then the same formula broken into named parts with each part explained.
3. Use absolute and relative references deliberately, and say why each is which.
4. Say what it returns when the lookup fails, when a cell is blank, and when a value is text that looks like a number.

If I am asking you to debug one: work outwards from the innermost function, state what each returns, and name the exact argument that is wrong. Common culprits to check first: a range that shifted when copied, text against number mismatch, trailing spaces, a lookup column that is not the leftmost, and a range that is not sorted where the function requires it.

Tool (Excel or Google Sheets):
Formula or goal:
Layout:

Making it yours

  • Paste your own material where the prompt asks for it. Everything above that line is instruction, not content.
  • Delete any rule that does not apply to you. A shorter prompt that fits beats a thorough one that does not.
  • If the answer comes back vague, add a line saying what you do not want. Constraints work better than encouragement.

Writing and reading queries. Drafting SQL from a plain question, making an inherited query legible, and moving it between dialects.

Related prompts

Explain this query to me line by lineYou inherited a 200-line query and need to understand it before changing it.Write the query from a plain-language questionYou know exactly what you want to know and not how to express it in SQL.Solve this with a window functionYou're about to write a self-join and suspect there's a cleaner way.Port this query to another SQL dialectMoving a query between Postgres, BigQuery, Snowflake, MySQL, or SQLite.Explain this query in three sentencesSomeone handed you a query and you need the gist before you touch it.Rewrite this without nested subqueriesFour levels of nesting and nobody can read it, including you.

Keep this one

Phrasery saves prompts with a right-click and puts them back the same way. Add this to your own library in one click.

Add to your browser — free