You need a column that automatically assigns a status label based on conditions — “High”, “Medium”, or “Low” based on a score, or “On Track”, “At Risk”, “Overdue” based on a date comparison. Nested IF statements handle this, and the newer IFS function makes it cleaner. Here’s how to build both, plus when to switch to a lookup table instead.
Basic Nested IF
Assign a priority label based on a numeric score:
| A | B | C | |
|---|---|---|---|
| 1 | Task | Score | Priority |
| 2 | Server Migration | 92 | Critical |
| 3 | Report Update | 65 | High |
| 4 | Documentation | 40 | Medium |
| 5 | Team Lunch | 15 | Low |
=IF(B2>=90, "Critical", IF(B2>=60, "High", IF(B2>=30, "Medium", "Low")))
Tests from highest to lowest: 90+ = Critical, 60-89 = High, 30-59 = Medium, below 30 = Low. Each IF’s FALSE branch contains the next test.
Order matters
Nested IFs test conditions in order and stop at the first TRUE. If you test B2>=30 before B2>=90, a score of 95 would match the >=30 test first and return “Medium”. Always test the most restrictive condition first.
IFS Function (Excel 2019+ / 365)
IFS removes the nesting and makes the logic easier to read:
=IFS(B2>=90, "Critical", B2>=60, "High", B2>=30, "Medium", TRUE, "Low")
Each pair is a condition and its result. The final TRUE, "Low" acts as the default (catches everything else). Much flatter and easier to extend than nested IFs.
Real-World Example: Project Status
Assign status based on multiple conditions — percentage complete and whether the deadline has passed:
=IF(B2=100%, "Complete", IF(C2
Checks completion first (100% = Complete), then whether the deadline has passed (Overdue), then whether it's within 7 days (At Risk), otherwise On Track.
Combining IF with AND/OR
For conditions that depend on multiple columns simultaneously:
Both conditions must be true
=IF(AND(B2>=50, C2="Active"), "Priority", "Standard")
Either condition can be true
=IF(OR(B2>=90, C2="Urgent"), "Escalate", "Normal")
When Nested IFs Get Too Deep: Use a Lookup Table
If you have more than 4-5 levels, nested IFs become unreadable. Use a lookup table instead:
1Create a reference table with thresholds and labels
| F | G | |
|---|---|---|
| 1 | Min Score | Label |
| 2 | 0 | Low |
| 3 | 30 | Medium |
| 4 | 60 | High |
| 5 | 90 | Critical |
2Use VLOOKUP with approximate match:
=VLOOKUP(B2, F2:G5, 2, TRUE)
The TRUE argument enables approximate matching — it finds the largest value that doesn't exceed B2. Score of 65 matches 60 → "High". This approach is easier to maintain: just add or change rows in the reference table.
Maintenance advantage
With a lookup table, changing thresholds means editing a cell value, not rewriting a formula. This is especially important when non-technical users need to adjust the scoring rules.
When Formulas Aren't Enough
Status logic in formulas works for straightforward rules. But real business workflows often need more:
- Complex rule chains — status depends on 5+ conditions across different columns and sheets
- Status history tracking — logging when each item changed status and who changed it
- Automated actions on status change — sending notifications, moving items to different lists, or triggering next steps
- Dashboard with status counts — visual summary of how many items are in each status across all projects
- Business rules that change — non-technical managers need to adjust rules without editing formulas
A custom VBA workflow tool applies complex rules, tracks status history, triggers actions, and provides dashboard summaries — with business rules managed through a simple settings sheet.
"David was thoughtful and spent the time to think through the project ahead of time so the product he developed was the best suited for the current needs of my business."
Shayna G. · Freelancer
Business Rules Too Complex for Formulas?
Tell us about your workflow logic and we'll build a tool that handles it cleanly.
Start a Project