Formuladrill

Logical Updated November 2026

IFS

IFS(test1, value1, [test2, value2], ...) checks the conditions in order and returns the value next to the first one that is TRUE. It replaces nested IF for tiers and grades. End with TRUE, "Other" as a catch-all, or the formula returns #N/A when nothing matches.

Syntax

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
PartWhat it is
logical_test1The first condition to check.
value_if_true1What to return when it is TRUE.
logical_test2, value_if_true2...Further pairs, up to 127. Checked in order; the first TRUE wins.

Examples

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

Result: A, B, C or F from a score in B2

TRUE as the last test catches everything that did not match.

=IFS(A2<10, 5, A2<50, 10, TRUE, 0)

Result: 5 when the order weight in A2 is under 10, 10 when under 50, otherwise 0

Tests are evaluated in order, so the brackets must go from smallest up.

=IFS(A2="", "", B2="Paid", "Closed", TRUE, "Open")

Result: Blank for empty rows, Closed for paid rows, Open for the rest

Common mistakes

  • #N/A: no condition was TRUE. Add TRUE, "default" as the last pair.
  • Wrong tier: conditions are in the wrong order. With >= tests go from highest to lowest, with < tests from lowest to highest.
  • #NAME?: IFS needs Excel 2019 or Microsoft 365. In older versions use nested IF.
  • An odd number of arguments (a test without a value) gives an error.

Practice IFS

These drills check your answer on live data, free, no account needed.

  • Shipping tiersShipping costs 5 for parcels up to 1 kg, 12 for parcels up to 5 kg and 25 for anything heavier. Use IFS to fill C2 to C4.
  • Letter gradesTurn the score into a grade: 90 or more is A, 80 to 89 is B, 70 to 79 is C, anything lower is F. Fill C2 and C3.

Questions

IFS or nested IF?

IFS is easier to read and edit and gives the same result. Nested IF is only needed in Excel 2016 or earlier.

Can IFS use AND and OR?

Yes, any test can be AND(...) or OR(...), for example =IFS(AND(A2="North", B2>1000), "Bonus", TRUE, "None").

Does IFS have an else?

Not as a keyword. Use TRUE as the final test and the default as its value.

Related

  • IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
  • AND, OR, NOTHow the Excel AND, OR and NOT functions test several conditions and return TRUE or FALSE, with examples inside IF, a between test and a not blank test.
  • CHOOSEHow the Excel CHOOSE function returns one of several values based on an index number, with examples for weekday names, quarters and scenario switches.
  • XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.

Previous: IFNext: IFERROR