The IFS function

Checks several conditions in order and returns the value for the first one that is TRUE. Use TRUE as the last test for "everything else".

How to use IFS

IFS is a flat list of if-this-then-that pairs. Picture a discount board in a shop: spend 500 or more and get 15% off, spend 200 or more and get 10%, otherwise nothing. IFS reads the board from the top and stops at the first line that fits.

You write the arguments in pairs: a test, then the result for it. =IFS(A2>=90,"High",A2>=60,"Mid",TRUE,"Low") checks 90 first, then 60, and lands on the last pair because TRUE is always true. That last pair is your "everything else" line. Without it, a value that fits no test returns #N/A.

Use IFS for grades, price bands, commission tiers or priority labels, where nested IFs would pile up parentheses. Order matters: put the strictest test first, because the first TRUE wins. For a simple yes or no, plain IF is shorter. When you compare one cell against exact values such as country codes, SWITCH is shorter still, since you name the cell only once. And if the bands live in a table, an approximate-match lookup such as =VLOOKUP(A2,E2:F4,2,TRUE) lets you change the bands without touching the formula.

Syntax

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
logical_test1
The first condition.
value_if_true1
The result when the first condition is TRUE.
logical_test2, value_if_true2 (optional)
More condition and result pairs, up to 127.

Examples

C1=IFS(A2>=90,"High",A2>=60,"Mid",TRUE,"Low")
ABC
1ScoreMid
262

In plain words: If the score in A2 is 90 or more, show High. If it is 60 or more, show Mid. In every other case, show Low.

C1=IFS(A2<50,"Low",A2<80,"Mid",A2>=80,"High")
ABC
1ScoreMid
262

Common IFS mistakes

  • IFS returns #N/A

    None of the tests was TRUE, and IFS has no default. Add a last pair that always matches: =IFS(A2>=90,"High",A2>=60,"Mid",TRUE,"Low").

  • Everyone gets the wrong band

    The first TRUE wins, so the order decides. In =IFS(A2>=60,"Mid",A2>=90,"High",TRUE,"Low") a score of 95 already passes the first test, so High is never reached. Put the strictest test first.

  • Excel says I entered too few arguments

    Every test needs a result right after it, so the argument count must be even. A test without a result, such as a stray TRUE at the end, is the usual cause.

  • A test like 60<=A2<90 never matches

    Spreadsheets cannot chain comparisons. 60<=A2 becomes TRUE or FALSE first, and that is then compared with 90. Use AND(A2>=60,A2<90) as the test, or rely on the order and write only A2<90 after the stricter tests.

Practice IFS

Read the guide

Questions

Does IFS work in older versions of Excel?

IFS is available in Excel 2019, Excel 2021, Microsoft 365 and Google Sheets. Excel 2016 and earlier do not have it and show #NAME?. In those versions, nest IFs or use a lookup table instead.

How do I use IFS with AND or OR?

Put AND or OR in a test, as in =IFS(AND(B2>=50,C2="Yes"),"Pass",TRUE,"Fail"). Each test can be as long as you like, as long as it ends up TRUE or FALSE.

How many conditions can IFS take?

Up to 127 pairs of a test and a result. Past a handful, a lookup table with VLOOKUP or XLOOKUP is easier to maintain.

How do I use IFS with text values?

Compare the cell with text in double quotes, =IFS(A2="Paid","Done",A2="Sent","Waiting",TRUE,"Open"). If a text never matches, look for extra spaces in the cell and remove them with TRIM.

Related functions