Excel IFS Function
last modified October 5, 2026
The IFS function is a logical function in Excel that checks one or
more conditions and returns the value that corresponds to the first TRUE
condition. It replaces long chains of nested IF functions with a
single, readable list of test/value pairs. This tutorial provides a
comprehensive guide to using the IFS function with detailed
examples. You'll learn basic syntax, the rules that decide which value is
returned, error handling, and how IFS compares with nested
IF and SWITCH.
IFS Function Basics
The IFS function evaluates its logical tests in order and returns
the value that belongs to the first test that is TRUE. If no test is TRUE it
returns the #N/A error, so a well-built formula normally ends with
a TRUE test that acts as a default.
| Component | Description |
|---|---|
| Function Name | IFS |
| Syntax | =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...) |
| Arguments | Pairs of logical tests and values, up to 127 pairs |
| Return Value | Value for the first TRUE test; #N/A if none is TRUE |
| Availability | Excel 2019, 2021, 2024, Microsoft 365, and Excel for the web |
This table breaks down the essential components of the IFS
function. It shows the function name, basic syntax format, argument structure,
return value behavior, and the Excel versions that support it.
| Argument | Meaning |
|---|---|
| logical_test1 | Required. The first condition, which must evaluate to TRUE or FALSE,
such as A1>90 or B1="East". |
| value_if_true1 | Required. The value returned when logical_test1 is TRUE. It
can be a number, text, a logical value, a cell reference, or another
formula, and it can be empty. |
| logical_test2, ... | Optional. Additional conditions, up to 127 tests in total. They are evaluated in the order you write them. |
| value_if_true2, ... | Optional. The value returned when the matching logical test is TRUE. Each value must follow its test. |
The arguments must come in pairs. Supplying a final logical test without its value produces the "You've entered too few arguments for this function" message, and Excel will not accept the formula. The function is limited to 127 test/value pairs; beyond that, move the thresholds into a lookup table.
Basic IFS Example
This example demonstrates the simplest use of the IFS function with numeric conditions.
=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "D")
This formula checks the value in cell A1 against several grade thresholds. A
value of 95 passes the first test and returns "A", a value of 85 returns "B", a
value of 75 returns "C", and any value that is not greater than 70 falls through
to the final TRUE test and returns "D". The TRUE acts
as the default case.
Ordering and Short-Circuiting
Because IFS returns the value for the first TRUE test, the order of
the tests decides the result. When tests overlap, place the most specific or
most restrictive test first. In the grade formula the tests run from the highest
threshold to the lowest; if they were reversed, every score above 70 would match
the A1>70 test before the higher thresholds were ever checked.
=IFS(A1>70, "Pass", A1>90, "Distinction") ' 95 returns "Pass"
With A1 equal to 95, the first test is already TRUE, so the formula returns
"Pass" and the "Distinction" branch is never used. Put the stricter test first:
=IFS(A1>90, "Distinction", A1>70, "Pass", TRUE, "Fail").
A common misconception is that IFS short-circuits, skipping the
remaining tests once one is TRUE. It does not. Unlike IF and
CHOOSE, IFS evaluates every test and every value in
the formula, even after it has found a match. The result is still the value of
the first TRUE test, but expensive or volatile calculations run whether or not
their branch is used. When skipping that work matters, use nested
IF or CHOOSE, which do short-circuit.
No Match: #N/A and the TRUE Default
IFS has no built-in value_if_false argument. When no
logical test is TRUE, it returns the #N/A error. This is different
from IF, which returns FALSE when its value_if_false
argument is omitted.
=IFS(A1=100, "OK", A1=200, "Warning", A1=300, "Error") ' 400 returns #N/A
The code 400 matches none of the tests, so the formula returns
#N/A. To provide a default, add TRUE as the final test
followed by the fallback value. The final test is always TRUE, but its value is
used only when every earlier test has failed, so it behaves like an
ELSE branch.
=IFS(A1=100, "OK", A1=200, "Warning", A1=300, "Error", TRUE, "Invalid")
Now 400 returns "Invalid". TRUE also catches empty cells, because a
blank cell is treated as zero and matches none of the equality tests. If you
want a blank result instead of a message, use TRUE, "".
IFS with Text Conditions
IFS can evaluate text conditions just as effectively as numeric
ones. Here's an example with text matching.
| A | B |
|---|---|
| Product | |
| Widget | |
| =IFS(A2="Widget", 10, A2="Gadget", 15, TRUE, 0) |
The table shows a simple spreadsheet with a product name in A2. The IFS formula in B3 checks the product type and returns a corresponding price.
=IFS(A2="Widget", 10, A2="Gadget", 15, TRUE, 0)
This formula returns 10 for "Widget", 15 for "Gadget", and 0 for any other
product. Text comparisons in Excel are case-insensitive, so
A2="Widget" also matches "widget" and "WIDGET". Use the
EXACT function when a comparison must respect case, for example
=IFS(EXACT(A2,"Widget"), 10, TRUE, 0).
IFS with Multiple Conditions
Each logical test can combine several conditions with the AND or
OR function. This example shows this capability.
| A | B | C |
|---|---|---|
| Sales | Region | |
| 5000 | East | |
| =IFS(AND(A2>4000,B2="East"), "Gold", AND(A2>3000,B2="West"), "Silver", TRUE, "Bronze") |
This table demonstrates IFS evaluating combined conditions using the AND function. Different status levels are assigned based on sales and region.
=IFS(AND(A2>4000,B2="East"), "Gold", AND(A2>3000,B2="West"), "Silver", TRUE, "Bronze")
This formula checks both sales amount and region. With A2 equal to 5000 and B2
equal to "East", the first test is TRUE and the result is "Gold". The second
test returns "Silver" for West region sales over 3000, and the final
TRUE returns "Bronze" otherwise. Because the first test is more
specific than the second, it must come first.
IFS for Date Ranges
IFS works well with date comparisons to categorize dates into
ranges or periods. This example shows date-based categorization.
| A | B |
|---|---|
| Date | |
| 2025-04-15 | |
| =IFS(A2<DATE(2025,1,1), "Previous Year", A2<DATE(2025,4,1), "Q1", A2<DATE(2025,7,1), "Q2", TRUE, "Later") |
The table shows how to use IFS with date functions to categorize dates into quarters. The DATE function creates specific dates for comparison.
=IFS(A2<DATE(2025,1,1), "Previous Year", A2<DATE(2025,4,1), "Q1", A2<DATE(2025,7,1), "Q2", TRUE, "Later")
This formula checks whether the date falls before 2025 ("Previous Year"), in Q1
(January through March), in Q2 (April through June), or later. With A2 equal to
April 15, 2025, the first two tests are FALSE and the third is TRUE, so the
result is "Q2". The DATE function creates the comparison dates and
avoids any ambiguity about date formats.
IFS with Error Handling
A logical test that evaluates to an error makes IFS return that
error, so it is wise to check for errors before the numeric tests. This example
shows how to handle potential errors in data.
| A | B |
|---|---|
| Value | |
| #N/A | |
| =IFS(ISERROR(A2), "Invalid", A2>100, "High", A2>50, "Medium", TRUE, "Low") |
The table demonstrates error handling in IFS. The ISERROR function checks for any error value before proceeding with numeric comparisons.
=IFS(ISERROR(A2), "Invalid", A2>100, "High", A2>50, "Medium", TRUE, "Low")
With A2 holding #N/A, the first test is TRUE and the formula
returns "Invalid". If the error test came after A2>100, the
comparison itself would return #N/A and the formula would fail, so
keep the error check first. When a test can produce an error, wrap that test in
IFERROR, for example IFERROR(VLOOKUP(...),FALSE), so
that a failed lookup becomes FALSE and evaluation moves on to the next test.
IFS vs Nested IF vs SWITCH
IFS provides a cleaner alternative to nested IF statements. The two
formulas below return the same grades.
=IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "D")))
=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "D")
Both formulas return "A" for a score above 90, "B" above 80, "C" above 70, and
"D" otherwise, but the IFS version is easier to read: each
condition and its result appear as a pair, and there is only one set of
parentheses to balance. The nested version has a natural default in its last
IF and short-circuits, while IFS needs the
TRUE default and evaluates every argument.
SWITCH is the other close relative. It compares one expression
against a list of values and returns the matching result, and it accepts a
default value as its final argument.
=IFS(A1=100, "OK", A1=200, "Warning", A1=300, "Error", TRUE, "Invalid") =SWITCH(A1, 100, "OK", 200, "Warning", 300, "Error", "Invalid")
Both return "Invalid" for a code that is not 100, 200, or 300. Use
SWITCH when you are matching one value against a list of exact
values, and use IFS when the tests involve comparisons such as
> or < or combine several expressions. Like
IFS, SWITCH does not short-circuit.
IFS Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| No test is TRUE and there is no TRUE default | #N/A error | Add TRUE as the final test with a fallback value |
| A logical test evaluates to text instead of TRUE or FALSE | #VALUE! error | Rewrite the test as a comparison, such as A1="Yes" |
| A logical test returns an error before any test is TRUE | The same error (#N/A, #DIV/0!, and so on) | Trap it with IFERROR or ISERROR, and put the error test first |
| A final logical test has no matching value | Too few arguments message | Supply the arguments in test/value pairs |
| More than 127 test/value pairs | Formula is rejected | Use a lookup table with XLOOKUP or VLOOKUP instead |
| Tests entered in the wrong order | A less specific branch wins | Put the most restrictive test first |
Most IFS problems come from ordering and from the missing default.
Order the tests from the most restrictive to the least restrictive, and end the
formula with TRUE and a fallback value unless you specifically want
#N/A to signal "no match". When a test can produce an error, guard
it with IFERROR rather than wrapping the whole formula, so that a
real "no match" result is not hidden.
The IFS function is a versatile tool for evaluating multiple
conditions in Excel. It replaces deeply nested IF formulas with a
flat, readable list of test/value pairs, and it handles numbers, text, dates,
and compound conditions built with AND and OR.
Remember that IFS returns the value for the first TRUE test, that
it returns #N/A when no test is TRUE, and that a final
TRUE test supplies a default. Keep the tests in the right order,
remember that text comparisons are case-insensitive, and be aware that
IFS evaluates every argument rather than short-circuiting.
Mastering IFS will make your logical formulas shorter, clearer, and
easier to maintain.
Author
List all Excel Formulas.