Excel guide · 4 min read

IFS vs nested IF vs SWITCH in Excel

Quick answerThey can all return one of several results. A nested IF works in every Excel version. IFS (Excel 2019 and later, including Microsoft 365) lists condition-result pairs with no stacked brackets, but returns #N/A if no condition is TRUE, so end it with TRUE,"default". SWITCH matches one value against a list of exact values, like region codes, and takes a default as its last argument.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	G	H
1	Rep	Sales	Region	From	Tier
2	Hana	84200	N	0	Bronze
3	Luis	61500	S	50000	Silver
4	Ines	47300	e	75000	Gold
5	Tariq	112900	W	100000	Platinum
6	Beth	75000	X
7	Femi		S
8	Cora	pending	N

Download this sheet as an Excel practice file (.xlsx)

The same tiers three ways

Each rep's sales in column B earn a tier: 100,000 and up is Platinum, 75,000 Gold, 50,000 Silver, anything else Bronze. As a nested IF, each new test goes in the "value if false" slot of the previous IF. It works, but four results already need three IFs and three closing brackets, and editing a band means counting them.

IFS writes the same logic as a flat list of pairs: test, result, test, result. Excel returns the result for the first TRUE test, exactly like the nested version, so both read from the top down.

=IFS(B2>=100000,"Platinum",B2>=75000,"Gold",B2>=50000,"Silver",TRUE,"Bronze")

Result: Gold

Gold for Hana's 84,200. =IF(B2>=100000,"Platinum",IF(B2>=75000,"Gold",IF(B2>=50000,"Silver","Bronze"))) returns the same.

Why IFS returns #N/A: the missing TRUE

A nested IF always has a final "value if false". IFS has no else argument. If none of the tests is TRUE, it returns #N/A. Ines sold 47,300, below every test in this version.

The fix is to make the last test TRUE itself. TRUE is always true, so it catches every row the earlier tests missed. Wrapping IFS in IFERROR also hides the #N/A, but it hides real errors too, such as a #VALUE! in the sales cell, so the TRUE catch-all is the better habit.

=IFS(B4>=100000,"Platinum",B4>=75000,"Gold",B4>=50000,"Silver")

Result: #N/A

#N/A, because 47,300 matches no test. Adding ,TRUE,"Bronze" at the end returns Bronze.

Order matters in both

IFS and nested IF stop at the first TRUE test. Put the loosest test first and it swallows everything: with B2>=50000 first, Hana's 84,200 never reaches the Gold test.

For "at least" bands, test from the highest threshold down. For "less than" bands, test from the lowest up. Also check the edges: Beth's 75,000 is Gold with >=75000 but Silver with >75000.

=IFS(B2>=50000,"Silver",B2>=75000,"Gold",TRUE,"Bronze")

Result: Silver

Silver, wrong for 84,200, because the first test is already TRUE.

SWITCH: one value against a list of exact matches

When you compare one cell with fixed values, such as region codes, SWITCH is the shortest: =SWITCH(C2,"N","North","S","South","E","East","W","West","Unknown"). The first argument is the value to check, then value-result pairs, and an optional last argument is the default. Without a default, a code that matches nothing returns #N/A, the same trap as IFS.

SWITCH only tests equality, so it cannot do the sales bands directly. A common workaround is =SWITCH(TRUE,B2>=100000,"Platinum",...), but at that point IFS says the same thing more plainly. Text comparisons with = are not case-sensitive, so in IF and IFS a lowercase "e" still matches "E". The engine behind our examples does not run SWITCH, so here is the same mapping with IFS.

=IFS(C4="N","North",C4="S","South",C4="E","East",C4="W","West",TRUE,"Unknown")

Result: East

East for Ines, even though her code is a lowercase "e". Beth's code X falls through to Unknown.

Many bands or codes: use a lookup table

If the thresholds change, or there are more than about five, none of the three is the best choice. Put the bands in a small table (G1:H5 here) and look them up. Changing a threshold then means editing one cell, not every formula.

XLOOKUP with match mode -1 finds the exact value or the next smaller one, which is exactly how "from" bands work. In versions without XLOOKUP, =VLOOKUP(B4,G2:H5,2,TRUE) does the same on a table sorted from smallest to largest. For codes, an exact-match lookup on a two-column code table replaces SWITCH.

=XLOOKUP(B4,G2:G5,H2:H5,,-1)

Result: Bronze

Bronze for 47,300, the largest "From" value that is not above it is 0.

Which one to pick

Use a nested IF when the file must open in Excel 2016 or older: IFS and SWITCH show #NAME? there. Use IFS for three to five ordered conditions, and always finish with TRUE. Use SWITCH when you map one cell's exact values to results. Use a lookup table when the bands or codes will change, or when other people maintain the file.

Limits rarely matter in practice: Excel allows 64 nested IFs, 127 test-result pairs in IFS and 126 value-result pairs in SWITCH. If you get close to any of them, you want a table.

Common mistakes

No TRUE at the end of IFS

Any row that matches no test returns #N/A. End with TRUE,"default" so every row gets a result.

Blank cells land in the catch-all

An empty cell counts as 0, so Femi with no sales gets Bronze. Put the blank test first: =IFS(B7="","",B7>=100000,"Platinum",...).

Text in a number column

Excel ranks text above every number, so "pending" >= 100000 is TRUE and Cora gets Platinum. Test with ISNUMBER first or clean the column.

Sharing IFS or SWITCH with Excel 2016 users

Those versions do not have the functions and show #NAME?. Use nested IF or a lookup table for files that travel.

FAQ

Is IFS better than nested IF?

It is easier to read and edit, because the conditions sit in a flat list with one closing bracket. It gives the same results as long as you end it with TRUE,"default". The trade-off is version support: IFS needs Excel 2019 or later.

Why does my IFS formula return #N/A?

None of the conditions was TRUE, and IFS has no else value. Add TRUE,"your default" as the last pair.

What is the difference between SWITCH and IFS?

SWITCH compares one value with a list of exact values; IFS evaluates a separate condition for each result, so it can test ranges like >=75000. Use SWITCH for codes, IFS for bands.

Which Excel versions have IFS and SWITCH?

Excel 2019, 2021, 2024, Microsoft 365 and Excel for the web. Excel 2016 and earlier show #NAME? for both.

How many conditions can IFS handle?

Up to 127 test-result pairs. Long before that, a lookup table is easier to maintain.

Functions used

Related guides

Updated November 3, 2026