IF, IFS, SWITCH, AND and OR Formulas Written with AI

Part of the free Module 13: Excel Formulas with AI · Lesson 7 of 9 · Full Excel course

Updated on 5 September 2026 · IF, AND and OR work in every version of Excel. IFS and SWITCH need Excel 2019, 2021, 2024 or Microsoft 365. LET needs Excel 2021 or later.

Writing conditional formulas with AI means describing a business rule in plain English and letting ChatGPT, Claude, Gemini or Copilot turn it into an Excel formula. AI is quick at this and usually picks a sensible function, but it guesses at whatever you leave out: the boundaries, the order of the tests and how blank cells are treated. Check those three things and the formula is safe to ship.

Turning a business rule into a formula: where AI is strong and where it slips

A conditional rule is easy to say and hard to write. “Anything over ten thousand pays five per cent” is one sentence, but Excel needs to know whether exactly 10,000 is over ten thousand, what happens to a blank row, and which test runs first. AI is excellent at the mechanical part: it knows the syntax, it closes the brackets and it will happily produce five different versions of the same rule. It is weak at reading your mind.

Four failures account for almost every wrong answer you will get.

  • Overlapping conditions. If you say “10,000 to 25,000 pays 5 per cent” and “25,000 and above pays 8 per cent”, the value 25,000 belongs to both bands. AI picks one silently.
  • Order of tests. IFS stops at the first condition that is TRUE. Ask for the bands in the wrong order and every row falls into the first one.
  • Greater than versus greater than or equal to. The single most common defect in AI formulas, and the one nobody notices until a boundary row appears in a report.
  • Text against number. If your codes are stored as text, or your amounts arrived from an export as text, comparisons behave in ways that look random.

The cure is not a better model. It is a prompt that states the boundary for every band, names the order of the tests, and says what a blank row must return. That is what the five-part template from the prompt lesson is for.

Which function to ask for: IF, nested IF, IFS, SWITCH, AND and OR

Tell the AI which function you want. If you do not, it will choose one, and its choice is often the one that fails on your version of Excel.

Function Use it when Version What to say in the prompt
IF One test, two outcomes Every version One condition, a value if true and a value if false
Nested IF Three or more bands, and the file must open in Excel 2016 Every version Use nested IF only, do not use IFS or SWITCH
IFS Three or more bands tested in order 2019 and later Use IFS and finish with TRUE as the last condition
SWITCH One value mapped to a fixed list of exact matches 2019 and later Use SWITCH and give a default value at the end
AND Every condition must be true at once Every version Use AND inside IF, not on its own
OR Any one condition is enough Every version Use OR inside IF, not on its own
Lookup table with XLOOKUP Bands or rates that change every quarter 365 and 2021 for XLOOKUP Put the bands in cells and look them up instead of hard-coding them

Readability matters as much as version. A four-band nested IF is still legible; a nine-band one is not, and neither you nor the next person will spot a wrong boundary buried in it. Once a rule has more than about five bands, or the bands change often, stop asking for a formula and ask for a lookup table instead. The lookup lesson in this course covers that pattern.

Ready-to-copy prompts for conditional formulas

Each prompt below uses the five-part template: goal, data layout, rules and edge cases, version and locale, output format. Copy it, change the sheet and column names, and paste it into ChatGPT, Claude, Gemini or the Copilot pane.

Prompt 1: a three-band commission with IFS

Goal: work out the commission earned by each sales rep.
Data: sheet "Sales", column A = Rep name (text), column B = Sales amount
(number), rows 2 to 200. Plain range, not an Excel Table.
Rules: below 10000 pays 2 per cent. From 10000 up to but not including
25000 pays 5 per cent. 25000 and above pays 8 per cent. Exactly 10000 must
pay 5 per cent and exactly 25000 must pay 8 per cent. A blank sales cell
must return an empty result, not zero.
Version and locale: Microsoft 365, English (United Kingdom), comma as the
argument separator.
Output: give the formula for C2 only, then one sentence naming the order in
which the tests run.

The formula you should get back:

=IF(B2="", "", IFS(B2<10000, B2*0.02, B2<25000, B2*0.05, TRUE, B2*0.08))
B: Sales Band applied C: Expected commission
8,000 2 per cent 160
10,000 5 per cent 500
24,999 5 per cent 1,249.95
25,000 8 per cent 2,000
41,200 8 per cent 3,296
(blank) none (blank)

The two boundary rows are the whole point. Remove the outer IF and the blank row returns 0, because an empty cell is read as zero and zero is below 10,000.

Prompt 2: a status flag with AND, and a variant with OR

Goal: flag invoices that need chasing.
Data: sheet "Invoices", column A = Invoice number, column B = Due date (a
real date, not text), column C = Paid (Yes or No from a drop-down), rows 2
to 500. Cell F1 holds the report date.
Rules: show Chase when the due date is before the report date AND Paid is
No. An invoice due exactly on the report date is not overdue. Every other
row shows OK.
Version and locale: Excel 2019, English (United Kingdom).
Output: give the formula for D2 only, with an absolute reference to F1, then
one sentence explaining it.
=IF(AND(B2<$F$1, C2="No"), "Chase", "OK")

Sample data with the report date in F1 set to 01/09/2026:

A: Invoice B: Due date C: Paid D: Expected result
INV-101 15/08/2026 No Chase
INV-102 15/08/2026 Yes OK
INV-103 01/09/2026 No OK
INV-104 31/08/2026 No Chase
INV-105 30/09/2026 Yes OK

INV-103 is the row that catches sloppy prompts. It is due on the report date, so it is not yet overdue, and < is correct where <= would be wrong. When any one condition is enough instead of all of them, swap the function:

=IF(OR(E2>50000, F2="New customer"), "Review", "Standard")

Note that AND and OR return a single TRUE or FALSE. They must sit inside IF if you want a label. Note also that AND(B2:B10>0) collapses a whole range to one answer; it does not test each row separately.

Prompt 3: SWITCH for mapping codes to names

Goal: turn one-letter region codes into full region names.
Data: sheet "Orders", column A = Region code, a single letter, rows 2 to
1000.
Rules: N is North, S is South, E is East, W is West. Anything else,
including a blank cell or a typo, must return Unknown. These are exact
matches, not ranges.
Version and locale: Excel 2019, English (United Kingdom).
Output: give the formula for B2 only.
=SWITCH(A2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")
A: Code B: Expected result
N North
W West
s South
X Unknown
(blank) Unknown

The lower-case s still returns South, because Excel comparisons ignore case. If case must matter you need EXACT instead, and you should say so in the prompt. The final lone argument is the default; drop it and an unmatched code returns #N/A.

Prompt 4: the same rule rewritten as nested IF for Excel 2016

Goal: the same three-band commission as before, but the workbook has to
open in Excel 2016 on a colleague's machine.
Data: sheet "Sales", column B = Sales amount, rows 2 to 200.
Rules: identical bands and boundaries as before. IFS, SWITCH and LET are not
available, so use nested IF only. A blank sales cell returns an empty
result.
Version and locale: Excel 2016, English (United Kingdom).
Output: give the formula for C2, then list the tests in the order they run.
=IF(B2="", "", IF(B2<10000, B2*0.02, IF(B2<25000, B2*0.05, B2*0.08)))

The results are identical to the IFS table above: 8,000 returns 160, 10,000 returns 500, 25,000 returns 2,000. The nesting reads inside out, so the innermost IF is the last band. Always ask for the ordered list of tests as well as the formula; it is far easier to audit five lines of English than four nested brackets.

Prompt 5: an IF that handles blanks properly

Goal: show a line total only when a quantity has actually been entered.
Data: sheet "Order lines", column A = Quantity, column B = Unit price, rows
2 to 200. Quantity is often blank while the order is being filled in.
Rules: if quantity is blank, return an empty text string so the column looks
clean. A quantity of 0 is a genuine value and must return 0, not a blank.
Version and locale: Microsoft 365, English (United Kingdom).
Output: give the formula for C2 only.
=IF(A2="", "", A2*B2)
A: Quantity B: Unit price C: Expected total
3 12.50 37.5
(blank) 12.50 (blank)
0 9.99 0
10 4.00 40

One caveat AI rarely mentions: the empty result is a text string, not a truly empty cell. SUM(C2:C5) ignores it and returns 77.5 as you would hope, but ISBLANK(C3) returns FALSE and COUNTA(C2:C5) counts four cells, not three. If a later step depends on real blanks, say so in the prompt.

Ask for the decision table first, then the formula

The most reliable way to get a correct conditional formula is to stop asking for a formula. Ask the AI to write the rule as a table of conditions and outcomes, agree the table, then ask for the formula that implements exactly that table. Errors become visible while they are still English.

Goal: work out the shipping fee for an order.
My rule in rough words: big orders ship free, medium orders pay a small fee,
small orders pay more.
Task: do not write a formula yet. First ask me for any threshold or amount I
have not given you. Then produce a two-column table, Condition and Outcome,
listing the tests in the exact order Excel should run them, with every
boundary stated as either "at least" or "less than".

After you supply the missing numbers, the agreed table looks like this:

Test order Condition (order total in B) Outcome: shipping fee
1 At least 5,000 0
2 At least 2,000 150
3 At least 500 250
4 Anything else 400

Now ask for the formula that matches the table exactly, in the same order:

=IFS(B2>=5000, 0, B2>=2000, 150, B2>=500, 250, TRUE, 400)

Test it on the boundaries: 5,000 returns 0, 4,999 returns 150, 2,000 returns 150, 1,999 returns 250, 500 returns 250, 499 returns 400. Because the tests descend, each row is caught by the first band it qualifies for. Reverse the order and every paying order would return 250.

Making AI formulas readable

A correct formula that nobody can read is a liability. Three requests turn dense output into something reviewable.

  1. Ask for named parts with LET (Excel 2021, 2024 and Microsoft 365 only). Add this line to your prompt: use LET to name the intermediate values so the formula reads like a sentence. 
    =LET(sales, B2, rate, IFS(sales<10000, 0.02, sales<25000, 0.05, TRUE, 0.08), sales*rate)

    The rate is now a named value you can read at a glance, and 41,200 still returns 3,296.

  2. Ask for line breaks. Add: format the formula on several lines, one condition pair per line. Excel accepts line breaks inside a formula. Press Alt+Enter in the formula bar to add them yourself, and drag the bottom edge of the formula bar down to see them all.
  3. Ask it to list the test order. Add: after the formula, list the tests in the order Excel evaluates them, with the boundary for each. Reading that list against your decision table is a ten-second review that catches almost every band error.

Worked example: a grading rule with an absent flag

A tutor marks ten students out of 100 and needs a letter grade, with absent students shown as Absent rather than F. Column A holds names and column B holds scores.

The agreed decision table: at least 90 is A, at least 75 is B, at least 60 is C, at least 40 is D, anything else is F, and a blank score is Absent.

  1. Write the prompt with the five parts, stating every band as at least and naming Microsoft 365 as the version.
  2. Enter the formula in C2 and fill it down to C11: 
    =IF(B2="", "Absent", IFS(B2>=90, "A", B2>=75, "B", B2>=60, "C", B2>=40, "D", TRUE, "F"))
  3. Check the four boundary rows by eye: 90, 75, 60 and 40 must all take the higher grade.
  4. Count the top grades in E2 with =COUNTIF(C2:C11,"A"). The result is 2.
  5. If the file must open in Excel 2016, ask for the nested version instead: 
    =IF(B2="", "Absent", IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=60, "C", IF(B2>=40, "D", "F")))))
A: Student B: Score C: Expected grade
Ravi 92 A
Meera 90 A
Sam 89 B
Aisha 75 B
Tom 74 C
Nina 60 C
Ali 45 D
Zoe 40 D
Ben 39 F
Kim (blank) Absent

Ten rows, six of which sit on a boundary. That is deliberate: a test set that contains only comfortable values proves nothing about an AI formula.

Tips and common mistakes

  • State every boundary in words. Write “at least 10,000” or “less than 10,000” in the prompt. Never write “over” or “between”, which AI resolves by guessing.
  • Name the order of the tests. IFS returns the first match, so descending or ascending order changes every answer.
  • Give the exact text values. If a column comes from a drop-down, paste the list into the prompt. Yes, Y and Paid are three different values.
  • Say the Excel version. Without it you will often get IFS, SWITCH or LET in a workbook that has to open in Excel 2016.
  • Never accept AND or OR on their own. They return TRUE or FALSE; a label needs them wrapped in IF.
  • Watch for Google Sheets syntax. AI sometimes returns IF(A2>10 AND B2="x", ...), which Excel rejects. Excel needs IF(AND(A2>10, B2="x"), ...).
  • Check your separator. Some regional settings use semicolons instead of commas. Say your locale in the prompt or swap them yourself.
  • Test a blank, a zero and both sides of every boundary before you trust the column.

Errors and how to fix them

Symptom Likely cause Fix
#NAME? on an IFS or SWITCH formula Excel 2016 or earlier does not have these functions, or the name is misspelt Ask the AI to rewrite it using nested IF only, or open the file in Excel 2019 or later
#N/A from IFS No condition was TRUE and there is no catch-all Add TRUE and the fallback value as the final pair
#N/A from SWITCH The value matched nothing and no default was supplied Add one extra argument at the end as the default
Message: you have entered too many arguments for this function An else value was added without a matching condition, so IFS no longer sees clean pairs IFS takes condition and value pairs; put TRUE in front of the last value
Every row falls into the top band The numbers are stored as text, and Excel treats any text as greater than any number Select the column, then Data > Text to Columns > Finish, or convert with VALUE
#VALUE! The formula compares text with a number, or a date is stored as text Convert the column first, or ask the AI to add an ISNUMBER guard
The column shows TRUE and FALSE instead of labels The AI returned a bare AND or OR Wrap it: =IF(AND(...), "Yes", "No")
A row that should be empty shows 0 A blank cell is read as zero by the first band test Add IF(A2="", "", ...) as the outermost test

Practice exercise

  1. Write a five-part prompt for a discount rule: orders of at least 100 units get 15 per cent, at least 50 units get 10 per cent, at least 20 units get 5 per cent, anything else gets nothing. Ask for IFS, then for a nested IF version for Excel 2016.
  2. Build a test row for each boundary: 100, 99, 50, 49, 20, 19, 0 and a blank. Confirm both formulas return the same eight answers.
  3. Use SWITCH to map five department codes to department names with a default of Unassigned. Add a lower-case code and a code that does not exist, and record what each returns.
  4. Write an AND rule that flags a training record as Expired when the expiry date is before a date in a fixed cell and the status is Active. Then change it to an OR rule and describe how the flagged rows change.
  5. Take your longest nested IF and ask the AI to rewrite it with LET and line breaks. Compare the two for readability, then check that both return identical answers on your test rows.

Key takeaways

  • AI writes conditional formulas well when the prompt fixes the boundaries, the test order and the treatment of blanks; it guesses whenever you leave those out.
  • Choose the function yourself: IF for one test, IFS for ordered bands, SWITCH for exact-match mapping, AND and OR inside IF for combined conditions, nested IF when the file must open in Excel 2016.
  • IFS and SWITCH need Excel 2019 or later; both return #N/A without a final catch-all.
  • Getting a decision table agreed before the formula catches band errors while they are still readable English.
  • Ask for LET names, line breaks and a written list of the test order to make the result reviewable.
  • Test both sides of every boundary, plus a blank and a zero, before you trust an AI formula in a live report.

Related lessons

Frequently asked questions

Can ChatGPT write an Excel IF formula for me?

Yes, and it is one of the tasks AI does best. Give it the sheet and column layout, every band with its exact boundary, the Excel version and the output format you want. The formula it returns is usually correct in structure. Check the boundary rows and the blank rows yourself before using it in a live workbook.

What is the difference between IF, IFS and SWITCH?

IF handles one test with two outcomes and can be nested for more. IFS takes a list of condition and value pairs and returns the first condition that is TRUE, which suits ordered numeric bands. SWITCH compares one value against a list of exact matches, which suits code lookups. IFS and SWITCH both need Excel 2019 or later.

Why does my IFS formula return #N/A?

Because no condition in the list evaluated to TRUE and there is no catch-all. Add TRUE followed by the fallback value as the last pair, for example IFS(B2>=90,"A",TRUE,"F"). SWITCH shows the same error when a value matches nothing and no default argument was supplied, so add one final argument as the default.

Does IFS work in Excel 2016?

No. IFS, SWITCH, TEXTJOIN, CONCAT, MAXIFS and MINIFS arrived in Excel 2019 and Microsoft 365. In Excel 2016 they return #NAME?. Say your version in the prompt and ask for nested IF instead. A workbook containing IFS still opens in 2016, but the cells show the error rather than a result.

How do I ask AI for a formula with multiple conditions?

Say whether all conditions must be true or any one is enough. All of them means AND, any one means OR, and both must sit inside IF to produce a label. List each condition on its own line in the prompt, with the exact text or number it compares against, and state what the row should show when no condition is met.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousLookup Formulas with AI: XLOOKUP, INDEX MATCH and Multi-CriteriaAll lessonsExcel Formulas with AINext →Text Cleaning Formulas with AI: TEXTSPLIT, TRIM and REGEX