Write an Excel or Google Sheets Formula from a Plain-English Description

Get a ready-to-paste Excel or Google Sheets formula built for your version and columns, with a step-by-step walkthrough and one test case to check it.

You getFormula, walkthrough and test case

Prompt

Prompt
Act as a spreadsheet analyst who writes Excel and Google Sheets formulas every day.

Your job: turn my plain-English description into a working formula. Before writing anything, ask me these questions in one message and wait for my answers:
1. Excel or Google Sheets? If Excel, which version (Microsoft 365, 2021, 2019 or older)?
2. What should the formula do, in one or two sentences?
3. Which columns are involved? Give column letters, header names and 3-5 sample rows.
4. Which cell will the formula go in, and will it be copied down or across?

Rules:
- Use only functions available in my app and version. If a newer function would be simpler, mention it as an alternative.
- Use the exact cell references from my sample, with $ locks wherever the formula will be copied.
- Handle blanks and errors explicitly and state what the formula returns in each case.
- Write one formula unless a helper column is clearly simpler; if so, say why.

Output format:
1. Formula: in a code block, ready to paste.
2. Walkthrough: a numbered list explaining each function and reference in the order Excel or Sheets evaluates them.
3. Test case: one row from my sample, the expected result, and how to check it by hand.
4. Common mistakes: 2 short bullets.

End with one line naming any assumption you made about my data.

Example output

At a glanceHow the price lookup formula worksINDEX/MATCH formula for Excel 2019, built up piece by piece
  1. Skip blank SKU rowsIF(B2="","", ...) keeps unused rows empty
  2. Find the SKU positionMATCH with 0 forces an exact match
  3. Lock the lookup range$ keeps Prices!$A$2:$A$250 fixed when filling down
  4. Return the priceINDEX pulls column B at the matched position
  5. Catch missing SKUsIFERROR shows "Not found" instead of #N/A
  6. Fill downB2 becomes B3, B4 and so on

Example for: an office manager on Excel 2019 who needs each order row to pull the unit price from a separate Prices sheet.

1. Formula

Paste into Orders!D2, then fill down:

=IF(B2="","",IFERROR(INDEX(Prices!$B$2:$B$250,MATCH(B2,Prices!$A$2:$A$250,0)),"Not found"))

Alternative: Excel 2019 does not have XLOOKUP. If you upgrade to Microsoft 365, this becomes =IF(B2="","",XLOOKUP(B2,Prices!$A$2:$A$250,Prices!$B$2:$B$250,"Not found")).

2. Walkthrough
  1. IF(B2="","", ...) checks the SKU cell first. If it is empty, D2 stays blank instead of showing an error on unused rows.
  2. MATCH(B2,Prices!$A$2:$A$250,0) finds the position of the SKU in the Prices list. The 0 forces an exact match.
  3. Prices!$A$2:$A$250 is locked with $ so the range does not shift when you fill down.
  4. INDEX(Prices!$B$2:$B$250, ...) returns the price in the same position MATCH found.
  5. IFERROR(..., "Not found") catches SKUs missing from the Prices sheet and shows "Not found" instead of #N/A.
  6. B2 has no $, so it becomes B3, B4 and so on as you copy down.
3. Test case
Orders rowOrder IDSKU (B)Qty (C)Expected D
310482TB-204612.50

Check by hand: go to the Prices sheet, press Ctrl+F, search TB-204. It sits in row 14 with 12.50 in column B. If Orders!D3 shows 12.50, the formula works. Then type a fake SKU such as ZZ-999 in an empty B cell: D should show "Not found".

4. Common mistakes
  • Trailing spaces in SKUs make exact matches fail. Wrap with TRIM in both sheets or clean the data with Find and Replace.
  • Leaving out the 0 in MATCH switches to approximate match and can return the wrong price without any error.

Assumption: each SKU appears only once on the Prices sheet and is stored as text in both sheets; if some SKUs are numbers, convert them so the types match.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for app and version first stops the model from suggesting XLOOKUP or LET to someone on Excel 2019.
  • Sample columns and the target cell mean the formula uses your real references and correct $ locks.
  • A worked test case from your own data lets you verify the result in seconds instead of trusting it blindly.

Use when you know what you want a cell to calculate but not the formula. Have your app and version, column letters and a few sample rows ready. Works in ChatGPT, Claude or Gemini.

Common edits

  • Ask for a version that returns 0 instead of blank so totals and charts work cleanly.
  • Request an array or FILTER formula that fills the whole column at once in Microsoft 365 or Google Sheets.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets