Stop Using Nested IFs: 3 Better Excel Formulas

Watch the 2-minute video:

Every help desk has seen this formula: an IF inside an IF inside an IF, with a row of closing brackets at the end. It works until someone adds one more condition, misses one bracket, and Excel answers with “There’s a problem with this formula.”

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

Modern Excel has three cleaner tools for this job. Here’s when to use each one, with the same examples as the video.

Download the practice workbook: excel-nested-ifs-sample.xlsx (attach the file when you publish). It has one sheet per example, with the formulas already in place.

1. IFS: a list of tests, in order

Use IFS when you have ranges that you check from top to bottom, like grades from a score.

=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

You write a test, then its result, again and again. Excel returns the result of the first test that is TRUE, so the order matters: put the highest band first.

The last pair, TRUE,"F", is the catch-all. TRUE is always true, so anything that fails every other test lands there. Leave it out and a score of 58 returns #N/A, because IFS returns #N/A when no condition is TRUE. Microsoft’s own documentation suggests the same TRUE trick.

Tip: you can split a long formula over several lines in the formula bar with Alt + Enter. It makes IFS and SWITCH much easier to read.

2. SWITCH: exact matches with a default

Use SWITCH when one cell can hold a known list of values, like a department code.

=SWITCH(B2,
  "IT",  "Information Technology",
  "HR",  "Human Resources",
  "FIN", "Finance",
  "Unknown")

The first argument is the value to check. Then come value and result pairs. The last argument with no pair is the default: if the code is MKT, the cell shows “Unknown” instead of #N/A. Without a default, SWITCH returns #N/A when nothing matches.

SWITCH is shorter than IFS here because you don’t repeat B2= in every test.

The tricky part: SWITCH(TRUE, …) for complex conditions

Plain SWITCH only compares exact values. SWITCH(B2, ">=90", ...) does not mean “90 or more”; it looks for the text “>=90”.

The trick is to put TRUE as the value to check. Then each “value” can be a full condition, and SWITCH returns the result of the first condition that is TRUE:

=SWITCH(TRUE,
  AND(B2>=90, C2>=80), "Honors",
  B2>=60,              "Pass",
  "Retake")

You can mix ranges, AND, OR and NOT in one readable list, and you still get a clean default at the end. IFS can do the same thing; many people prefer SWITCH(TRUE) because the default is built in.

3. XLOOKUP with a band table

When the bands change often (bonus rates, commission tiers, shipping prices), don’t hard-code them in any formula. Put them in a small table:

=XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, , -1)

• B2: the sales to look up.
• $E$2:$E$5: the “From” column of the band table.
• $F$2:$F$5: the bonus to return.
• The empty 4th argument skips “if not found”.
• -1 is the match mode: exact match, or the next smaller value. 12,400 is not in the table, so XLOOKUP takes 10,000 and returns 5%.

Two rules: the band table must start at the lowest possible value (0 here), and the $ signs keep the table fixed when you fill the formula down. Now when the bonus for the 10,000 band changes to 6%, you edit one cell in the table and every bonus updates.

Which Excel do you need?

On older versions, nested IF still works, and INDEX + MATCH (or VLOOKUP with TRUE) can do the band lookup. If you share a workbook with someone on Excel 2016, they will see #NAME? instead of these functions.

Checklist

• [ ] Ranges checked in order? Use IFS, highest band first, and end with TRUE, "default".
• [ ] One cell with a known list of values? Use SWITCH with a default as the last argument.
• [ ] Mixed conditions (ranges plus AND/OR)? Use SWITCH(TRUE, …).
• [ ] Bands that change? Put them in a table and use XLOOKUP with match mode -1.
• [ ] Check that everyone who opens the file has Excel 2019+ (IFS, SWITCH) or 2021+ (XLOOKUP).

Watch the video:

Take away support: IT fixes, delivered in under two minutes. By Abd Almouhimen Shamout, mouhimen.dev.

Sources

• Microsoft Support, IFS function: https://support.microsoft.com/en-us/excel/functions/ifs-function
• Microsoft Support, SWITCH function: https://support.microsoft.com/en-us/excel/switch-function
• Microsoft Support, XLOOKUP function: https://support.microsoft.com/en-us/excel/functions/xlookup-function
• Microsoft Support, IF function (nested formulas and avoiding pitfalls): https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8