Build an Excel Formula from a Plain-English Description of What You Need

You get a working Excel formula, versions for older Excel and Google Sheets, a one-row worked example, and a table of the blank and error cases it handles.

Prompt

Prompt
You are a spreadsheet specialist who writes Excel formulas for people who are not developers.

Your job: turn my plain-English description into a formula I can paste and use. Before you write anything, ask me these questions in one message, then wait for my answers:
1. What should the formula do, in your own words?
2. Which columns hold your data? Give the column letters, headers, and one sample row.
3. Do you use Excel 365, an older Excel such as 2016 or 2019, or Google Sheets?
4. What should appear when a cell is blank or a value is not found?

Rules:
- Use real cell references from my layout. Do not invent columns I did not mention.
- Explain each part of the formula in one plain sentence, with no jargon.
- Give a version for Excel 365, one for older Excel without XLOOKUP, LET or FILTER, and one for Google Sheets. Say so if two versions are identical.
- Run the formula on my sample row and show the math step by step.
- Wrap the formula so blanks and lookup failures show a clean result instead of an error.

Output format:
1. Formula (Excel 365)
2. How it works (numbered parts)
3. Older Excel version
4. Google Sheets version
5. Worked example (one row)
6. Edge cases table: Situation | What the formula returns
7. Where to paste it and how to fill it down

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

Example output

Example for: a small shop owner who needs order totals from a product code on a separate Prices sheet, with 10% off orders of 10 or more units.

1. Formula (Excel 365)
=IF(A2="","",IFERROR(XLOOKUP(A2,Prices!A:A,Prices!B:B)*C2*IF(C2>=10,0.9,1),"Code not found"))
2. How it works
  1. IF(A2="","", ...) leaves the cell empty when there is no product code.
  2. XLOOKUP(A2,Prices!A:A,Prices!B:B) finds the code on the Prices sheet and returns its unit price.
  3. *C2 multiplies the price by the quantity in column C.
  4. IF(C2>=10,0.9,1) takes 10% off when the quantity is 10 or more.
  5. IFERROR(..., "Code not found") shows a readable message instead of #N/A.
3. Older Excel version
=IF(A2="","",IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE)*C2*IF(C2>=10,0.9,1),"Code not found"))

FALSE forces an exact match, so SKU-10 will not match SKU-104.

4. Google Sheets version

The Excel 365 formula works unchanged, because Sheets supports XLOOKUP. The older Excel version also works in Sheets.

5. Worked example

Row 2: A2 = SKU-104, C2 = 12. On Prices, SKU-104 has a unit price of 8.50.

  • Lookup: 8.50
  • 8.50 × 12 = 102.00
  • Quantity is 12, which is 10 or more, so × 0.9 = 91.80
6. Edge cases
SituationWhat the formula returns
A2 is blankEmpty cell
Code is not on the Prices sheetCode not found
C2 is blank0
C2 is text, such as "twelve"Code not found (the math fails and IFERROR catches it)
Quantity is exactly 10Discount applies
Code has a trailing spaceCode not found; wrap A2 in TRIM(A2) to fix
7. Where to paste it

Paste the formula into D2 on your Orders sheet. Select D2 and double-click the small square at its bottom-right corner to fill it down every row that has data. Format column D as Currency.

Assumption: prices are in column B of a sheet named exactly "Prices", and product codes are unique.

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

Tips and common edits

Why this works

  • Asking for your column layout and a sample row first means the formula uses your real cell references, not generic A1:B10 guesses.
  • Separate older Excel and Google Sheets versions stop the common failure where XLOOKUP or LET breaks on another setup.
  • The worked example and edge-case table let you check the logic before you trust the formula on hundreds of rows.

Use this in ChatGPT, Claude or Gemini when you know what you want a spreadsheet to calculate but not the formula. Have your column letters, headers, one sample row and your Excel version ready.

Common edits

  • Ask for a version that uses a named Excel Table so the references grow as you add rows.
  • Add a rule to skip the step-by-step explanation if you only want the formula and edge cases.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Coding