Nested IF vs IFS in Excel: examples and when to use each

The short answer

• Same logic, same result. Both test conditions from left to right and return the result of the first test that is TRUE.
• IFS is easier to read and edit once there are three or more outcomes: no nested brackets, one pair per band.
• Nested IF works everywhere. IFS needs Excel 2019 or later (including Microsoft 365 and Excel for the web). In the one-off purchase of Excel 2016 and older it returns #NAME?.
• With many tiers, neither is best. A lookup table with an approximate XLOOKUP beats both, see below.

How a nested IF works

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

The first IF tests whether the score is at least 90. If it is, the formula returns "A" and stops. If not, Excel moves into the second IF, which tests 80, and so on. The last argument, "D", is the result when no test is TRUE. Ava's 92 gets an A, Chloe's 85 a B, Ben's 79 and Farid's 74 a C and Diego's 68 a D. Excel allows up to 64 nested IFs, but anything beyond three or four levels is hard to check.

The same logic with IFS

=IFS(D2>=$A$13,$B$13,D2>=$A$12,$B$12,D2>=$A$11,$B$11,TRUE,$B$10)

IFS takes up to 127 pairs of a test and a result. There is no "else" argument, so the last pair is TRUE,$B$10: a test that is always true, used as the fallback. Without it, a value that matches none of the tests returns #N/A. Here the rate comes from the tier table: 8% from $150,000 of sales, 6% from $100,000, 4% from $50,000 and 2% below that. Ava's $156,400 earns 8%, Chloe's exactly $100,000 earns 6% because the test is >=, and Diego's $47,200 earns 2%.

Worked example: grades with a nested IF, rates with IFS

Scores and sales are inputs (yellow). Column C uses a nested IF, column E uses IFS on the tier table in A10:B13. All names and figures are fictional.

Worked example: grades with a nested IF, rates with IFS

	A	B	C	D	E
1	Sales rep	CSAT score	Grade (nested IF)	Q3 sales	Rate (IFS)
2	Ava Chen	92	A	156,400	8%
3	Ben Okafor	79	C	98,500	4%
4	Chloe Dubois	85	B	100,000	6%
5	Diego Ramos	68	D	47,200	2%
6	Emma Lind	90	A	121,750	6%
7	Farid Haddad	74	C	63,900	4%
8
9	Sales from	Rate
10	0	2%
11	50,000	4%
12	100,000	6%
13	150,000	8%
14
15	Total commission: sales × rate (SUMPRODUCT)				33,257.00
16	Chloe's rate with XLOOKUP, next smaller match				6%
17	Number of A grades				2
18	Ava's grade with the tests in the wrong order				C

IFS and XLOOKUP need Excel 2019 or Microsoft 365 (XLOOKUP: Excel 2021 or Microsoft 365). Google Sheets supports both.

Why the order of the tests matters

Both formulas stop at the first TRUE test. With "at least" thresholds, test the highest band first. Row 18 shows what happens in the wrong order: =IF(B2>=70,"C",IF(B2>=80,"B",IF(B2>=90,"A","D"))) gives Ava a C, because 92 is also at least 70 and the A test is never reached. No error appears, the result is simply wrong. With "below" thresholds (<) the rule flips: start with the lowest band.

Keep the thresholds in a table

The IFS formula refers to the tier table in A10:B13 instead of typing 150000 and 8% into the formula. When the commission plan changes, you change four cells, not nine formulas, and anyone can see the plan at a glance. Lock the table with $ so the references stay put when you copy the formula down. The total commission is then one SUMPRODUCT of sales and rates: $33,257.00.

When XLOOKUP or SWITCH is the better choice

For tiers, an approximate lookup is shorter than any IF: =XLOOKUP(D4,A10:A13,B10:B13,,-1) returns the rate of the largest threshold that is not above the sales figure, 6% for Chloe. Adding a fifth tier means adding a row, not editing the formula. In older Excel, VLOOKUP with TRUE as the last argument does the same if the table is sorted ascending (see XLOOKUP vs VLOOKUP).

SWITCH is for exact values, not ranges: =SWITCH(A2,"N","North","S","South","Unknown") translates codes, and unlike IFS it has a default as its last argument. To count or sum by the bands afterwards, use COUNTIF or SUMIFS on the result column: two A grades here.

Common mistakes

• Testing the lowest band first. With >= thresholds the first test catches everything above it. Start with the highest band.
• No TRUE fallback in IFS. A value that matches no test returns #N/A. End with TRUE, and the default result.
• > instead of >=. "From $100,000" includes $100,000. With >, a rep who hits the threshold exactly lands one tier too low.
• Numbers in quotes. B2>="90" compares a number with text, and in Excel any text counts as larger than any number, so the test is always FALSE. Write B2>=90.
• Thresholds typed into the formula. Hard-coded limits are easy to miss when the plan changes. Keep them in a table and refer to it with absolute references.
• IFS in a file for older Excel. Excel 2016 and earlier show #NAME?. Use a nested IF or a lookup if the file is shared widely.