Excel IFS Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 39 of 105 · Full Excel course

The Excel IFS function checks several conditions in order and returns the value paired with the first condition that is TRUE. IFS in Excel replaces long nested IF formulas with a flat, readable list of test/result pairs, which makes grading scales, commission tiers and status labels far easier to write and maintain. It returns #N/A if no condition is met.

Table of Contents

IFS syntax

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

Arguments

Argument	Required	Meaning
logical_test1	Required	The first condition to evaluate, e.g. B2>=90.
value_if_true1	Required	The result returned when logical_test1 is TRUE.
logical_test2, value_if_true2 …	Optional	Further pairs, up to 127 in total. Tests are evaluated top to bottom and stop at the first TRUE.

IFS is available in Excel 2019, Excel 2021, Excel 365 and Excel for the web. Excel 2016 users need an Office 365 subscription; the perpetual Excel 2016 licence does not include it.

Step-by-step example

Convert a score in B2 into a letter grade:

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

Excel tests B2>=90. With 84 in B2 that is FALSE, so it moves on.
It tests B2>=80. TRUE, so IFS returns “B” and stops; the remaining tests are never evaluated.
The final pair, TRUE, “F”, acts as a catch-all for anything below 60.

Order matters: because IFS stops at the first TRUE, thresholds must be listed from highest to lowest (or lowest to highest with < tests).

Practical use cases

1. Commission rates

=C2*IFS(C2>100000, 10%, C2>75000, 7%, C2>50000, 5%, TRUE, 2%)

2. Delivery time by order size

=IFS(D2>1000, "4 weeks", D2>500, "3 weeks", D2>100, "2 weeks", TRUE, "1 week")

3. Status label with a blank guard

=IFS(E2="", "", E2<TODAY(), "Overdue", E2=TODAY(), "Due today", TRUE, "Upcoming")

Putting the blank test first prevents empty rows from being labelled Overdue.

4. Combine with AND / OR

=IFS(AND(F2>=50, G2="Yes"), "Eligible", OR(F2<50, G2="No"), "Not eligible", TRUE, "Review")

5. Exact matches: consider SWITCH instead

=SWITCH(A2, "HR", "Human Resources", "FIN", "Finance", "IT", "Technology", "Other")

When every test is “cell equals value”, SWITCH is shorter. IFS is for ranges and mixed conditions.

Common mistakes and errors

#N/A – no test returned TRUE. Add a final TRUE, “default” pair.
Wrong result from ordering – testing B2>=60 before B2>=90 makes every high score a D. Arrange thresholds so the most restrictive test comes first.
#VALUE! – a test does not produce TRUE or FALSE, or the arguments are not in pairs (an odd count).
#NAME? – the workbook is open in Excel 2016 or earlier without IFS. Use nested IF or a lookup table for compatibility.
Too many tiers – beyond eight or ten pairs, an approximate-match VLOOKUP or XLOOKUP against a small table is easier to update.

Tips and best practices

Order tests from most to least restrictive and always finish with TRUE, “default”.
Use Alt+Enter in the formula bar to place each test/value pair on its own line for readability.
Move long scales to a table and use VLOOKUP with approximate match or XLOOKUP with match_mode -1; changing a threshold then needs no formula edit.
Test boundary values (exactly 90, exactly 60) to confirm >= versus > behaves as intended.
Check compatibility before sharing; IFS returns #NAME? in Excel 2016 perpetual and earlier.