IFS and SWITCH Functions

Excel Guides / Logical

Logical

Replace long chains of nested IFs with one function that reads from top to bottom.

Updated 12 Oct 2026 Ā· 4 min read Ā· By Sky Dashboards

A nested IF with four or five levels works, but it is hard to read and easy to break with one misplaced bracket. IFS and SWITCH do the same jobs in a flatter form. IFS checks a list of conditions in order. SWITCH compares one value against a list of options. Both need Microsoft 365 or Excel 2019 or later.

IFS syntax

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Argument Required? What it does
logical_test1 Required The first condition to check.
value_if_true1 Required The result if logical_test1 is TRUE.
logical_test2, value_if_true2, ... Optional More condition and result pairs, up to 127 pairs in total.

Each condition sits next to its result, so there are no nested brackets to count. Excel checks the conditions in order and returns the result for the first one that is TRUE.

Add a TRUE catch-all

If none of the conditions is TRUE, IFS returns #N/A. End the list with TRUE as the last test and your fallback as its result. TRUE is always true, so it catches anything the earlier tests missed.

Example: label customers by tier

A gift shop sorts its customers into loyalty tiers by annual spend:

Tier Annual spend
Gold $2,000 or more
Silver $1,000 to under $2,000
Bronze $250 to under $1,000
Member Under $250
C4fx=IFS(B4>=2000,"Gold",B4>=1000,"Silver",B4>=250,"Bronze",TRUE,"Member")
A B C
1 Customer Spend Tier
2 Ella Park $2,450 Gold
3 Noah Kim $980 Bronze
4 Zara Ali $1,000 Silver
5 Tom Baker $120 Member
6 Ivy Chen $310 Bronze

The tests run from the highest threshold down. Zara's $1,000 fails the Gold test, passes the Silver test and stops there.

Compare the nested IF version: =IF(B4>=2000,"Gold",IF(B4>=1000,"Silver",IF(B4>=250,"Bronze","Member"))). It is the same logic, but with three closing brackets to keep track of, and it is harder to edit.

Tip

Order matters exactly as it does in a nested IF. With >= tests, start with the highest threshold. Put B4>=250 first and every customer on $250 or more would be labelled Bronze.

The same pattern grades test scores for a tutor or a training course:

=IFS(B2>=85,"A",B2>=70,"B",B2>=55,"C",TRUE,"Resit")

A score of 72 returns B. A score of 40 falls through to the TRUE catch-all and returns Resit.

SWITCH syntax

=SWITCH(expression, value1, result1, [default_or_value2, result2], ...)

Argument Required? What it does
expression Required The value to compare, usually a cell or a short formula. It is calculated once.
value1 Required The first value to compare against expression.
result1 Required What to return if expression equals value1.
default_or_value2, result2, ... Optional More value and result pairs, up to 126. A single argument left on its own at the end is the default, returned when nothing matches.

SWITCH only tests for an exact match. It is built for codes, categories and other fixed values, not ranges. If nothing matches and there is no default, it returns #N/A.

C5fx=SWITCH(B5,"IG","Instagram","WEB","Website","MKT","Market stall","Other")
A B C
1 Order Channel Channel name
2 #1041 IG Instagram
3 #1042 WEB Website
4 #1043 MKT Market stall
5 #1044 TT Other

TT is not in the list, so SWITCH returns the default, Other.

The odd argument at the end, "Other", is the default. Without it, the TT order would show #N/A.

=SWITCH(WEEKDAY(A2),1,"Weekend",7,"Weekend","Weekday")

WEEKDAY returns 1 for Sunday and 7 for Saturday, so this labels any date in A2 as Weekend or Weekday.

SWITCH(TRUE) for ranges

SWITCH cannot test >= directly, but there is a well-known workaround. Make the expression TRUE and write each value as a test. SWITCH returns the result for the first test that equals TRUE:

=SWITCH(TRUE,B4>=2000,"Gold",B4>=1000,"Silver",B4>=250,"Bronze","Member")

The tier formula again. The final "Member" is the default, so no TRUE catch-all is needed.

It works, but IFS says the same thing more plainly. Use whichever your team finds easier to read.

When IFS or SWITCH beats nested IF

Situation Best choice
Two possible outcomes IF
Three or more outcomes from thresholds or mixed conditions IFS
One cell compared against a list of exact values SWITCH
Many bands, or bands that change often A lookup table with XLOOKUP
The file must open in Excel 2016 or earlier Nested IF

SWITCH has one more advantage: the expression is written once. A nested IF that tests the same calculation at every level repeats it each time, so =IF(WEEKDAY(A2)=1,"Weekend",IF(WEEKDAY(A2)=7,"Weekend","Weekday")) calls WEEKDAY twice. SWITCH calls it once.

When the list of bands grows past five or six, or changes every season, move the bands into a table on the sheet. An approximate-match XLOOKUP then picks the right tier, and changing a threshold means editing a cell, not a formula.

Excel versions and common errors

IFS and SWITCH work in Excel 2019, Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Standalone Excel 2016 and earlier versions do not have them. Open the file there and the formulas show an _xlfn. prefix in front of the function name and return #NAME? when they recalculate.

  • #N/A from IFS: no test was TRUE. Add TRUE and a fallback result at the end.
  • #N/A from SWITCH: no value matched and there is no default. Add one as the final argument.
  • The wrong tier: the tests are in the wrong order, so an earlier, wider test catches values meant for a later one.
  • SWITCH misses a number: SWITCH compares types as well as values, so the text "1" does not match the number 1. Check for numbers stored as text.

Tip

Tier labels pair well with conditional formatting. Use Home > Conditional Formatting > Highlight Cells Rules > Equal To... to give each label its own colour, and the table reads at a glance.

Frequently asked questions

What happens if no condition in IFS is true?

IFS returns #N/A. Add TRUE as the last test, followed by a fallback result, to catch everything else.

Can SWITCH handle ranges such as greater than 100?

Not directly, because it only checks for equal values. Use SWITCH(TRUE,...) with a test in each value slot, or use IFS.

How many conditions can IFS take?

Up to 127 pairs of tests and results. SWITCH takes up to 126 pairs of values and results.

Why does IFS show #NAME? on another computer?

That copy of Excel is older than Excel 2019 and does not have the function. Use a nested IF or a lookup table if the file has to open there.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides