Organize information

Build a multi-condition Excel IF formula

Turn ordered business rules into a formula with explicit precedence and edge cases

5 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Business rulesColumns and data typesExample cases

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Create an Excel IF, IFS, or SWITCH formula for these rules:
[rules]

Workbook layout:
[layout]

Example cases and expected results:
[examples]

First convert the rules into an ordered decision table. Identify overlaps, gaps, ambiguous boundaries, and the fallback result. Do not write the formula until precedence is explicit. Then return the formula for the first data row, explain why IF, IFS, or SWITCH is appropriate, and test every branch including blanks, equal-to-boundary values, and a case matching more than one condition. Do not hide unexpected inputs unless a fallback is explicitly authorized.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Business rules: Status is 'Review' when score is blank. Status is 'Priority' when score is at least 90 and overdue days are greater than 0. Otherwise: score 90–100 is 'High', 70–89 is 'Medium', below 70 is 'Low'. Invalid scores below 0 or above 100 must show 'Invalid'.
Columns and data types: C: Score (number or blank); D: Overdue Days (whole number); E: Status. Data starts on row 2.
Example cases: C2=95,D2=2 => Priority; 95,0 => High; 90,0 => High; 70,0 => Medium; 69,0 => Low; blank => Review; 105 => Invalid.

Example output

Decision order: blank → Review; outside 0–100 → Invalid; score ≥90 and overdue >0 → Priority; score ≥90 → High; score ≥70 → Medium; otherwise → Low. The invalid check must precede the threshold branches, and Priority must precede High because both match a score of 90 or more.

E2 formula =IFS(C2="","Review",OR(C2<0,C2>100),"Invalid",AND(C2>=90,D2>0),"Priority",C2>=90,"High",C2>=70,"Medium",TRUE,"Low")

IFS is clearer than deeply nested IF because the rules are ordered predicates rather than a single exact-value mapping. Tests: 95/2 → Priority; 95/0 and 90/0 → High; 70/0 → Medium; 69/0 → Low; blank → Review; 105 → Invalid. Confirm whether a negative overdue value is valid and whether numeric text should be accepted before filling down.

Why this works

1
An ordered decision table exposes precedence before nested logic makes it hard to see.
2
Testing overlaps and exact boundaries catches the most common classification mistakes.