IFS Generator
Test several conditions in order and return the result of the first one that is true, without nesting IF statements.
IFS
Test several conditions in order and return the result of the first one that is true, without nesting IF statements.
Checked first. The first condition that is TRUE wins, so start with the most restrictive.
Returned when the first condition is true. Text needs quotes.
Only reached when the first condition is false.
Returned when the second condition is true. Text needs quotes.
Only reached when the first two conditions are false.
Returned when the third condition is true. Text needs quotes.
Paired with a final TRUE test so anything that matched nothing lands here instead of returning #N/A.
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")Worked Example
Turn a test score in A2 into a letter grade with a fallback for anything below 70.
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")Returns: A score of 84 returns "B"; 95 returns "A"; 62 falls through to the TRUE catch-all and returns "F".
Checks Before You Paste
- •IFS has no built-in else argument. The final TRUE in this formula is the standard workaround: TRUE always evaluates as true, so its result is returned whenever nothing above matched. Remove it and unmatched rows return #N/A.
- •IFS stops at the first condition that is TRUE, so order is everything. Listing A2>=70 before A2>=90 would label a score of 95 as "C", because 95 satisfies the >=70 test first.
- •Arguments always come in test/result pairs. Unlike SWITCH, IFS has no lone trailing default slot, which is why the catch-all above is written as the pair TRUE, result. Leave an odd argument over anywhere and Excel answers with "You've entered too few arguments for this function", which almost always means a missing result rather than a missing test.
How IFS works
Availability: Excel 2019, Microsoft 365 and Excel for the web; also in Google Sheets. Excel 2016 and earlier do not have it - a workbook saved with IFS opens there showing _xlfn.IFS and returns #NAME?.
IFS walks a list of test/result pairs from left to right and returns the result belonging to the first test that evaluates to TRUE. Everything after that point is ignored. It exists to replace deep nests of IF, where the closing brackets pile up and a change in the middle of the chain means re-counting parentheses.
Because IFS stops at the first success, the order of the pairs is the logic. Bands have to be listed from the tightest to the loosest: a score of 95 tested against A2>=70 first would be labelled with the >=70 result and never reach the >=90 pair. That single rule accounts for most wrong answers people get from IFS, and it is why grade, tax and discount tables are written descending.
There is no default argument. If every test comes back FALSE, IFS returns #N/A. The standard workaround is to end with the pair TRUE, result - TRUE is always true, so it acts as an else branch. IFS is also strictly pairwise, unlike SWITCH, which does have a lone trailing default; leaving an odd argument at the end of an IFS gets rejected as you type it.
The tests are ordinary logical expressions, so they can compare different columns, call AND and OR, or use ISBLANK and ISNUMBER. All of them are evaluated before IFS chooses, which means an error produced by a later test can surface even when an earlier test would have matched. Text comparisons are case-insensitive, so "north" matches "North"; wrap the test in EXACT if that matters.
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)| Argument | Required | What it does |
|---|---|---|
logical_test1 | Required | The first condition, checked before any other. Anything that resolves to TRUE or FALSE. |
value_if_true1 | Required | What the formula returns when the first test is TRUE. Text needs quotes; it can also be a cell reference or another formula. |
logical_test2, value_if_true2, ... | Optional | Further test/result pairs, checked in order and only reached when everything before them was FALSE. Excel allows 127 pairs. Ending with TRUE as the last test creates the catch-all that IFS otherwise lacks. |
More worked examples
Parcel shipping bands by weight in kilograms, with A2 holding the weight.
=IFS(A2<=1, 4.5, A2<=5, 7.5, A2<=20, 12, TRUE, 25)Returns: A2 = 5 returns 7.5; A2 = 5.2 returns 12; A2 = 40 falls through to the TRUE pair and returns 25.
The tests run in ascending order here because each one is an upper bound. Bands built on >= have to be listed descending instead.
A commission tier from annual revenue in B2, written without a catch-all.
=IFS(B2>=100000, "Tier 1", B2>=50000, "Tier 2", B2>=10000, "Tier 3")Returns: B2 = 64,000 returns "Tier 2". B2 = 2,400 matches nothing and returns #N/A, and so does a blank B2.
Add TRUE, "Below threshold" as a final pair to turn that #N/A into something a reader can act on.
Classifying what is in a data-entry cell before anything else is calculated from it.
=IFS(ISBLANK(A2), "Missing", ISNUMBER(A2), "Number", TRUE, "Text")Returns: An empty A2 returns "Missing", A2 = 42 returns "Number", A2 = N/A typed as text returns "Text".
Tests do not have to be comparisons - any function that returns TRUE or FALSE works.
Common mistakes
- #N/A because nothing matched
- IFS has no else. When every test is FALSE - including on blank rows, which fail almost any numeric test - the cell shows #N/A. Finish the argument list with TRUE and the fallback value rather than wrapping the whole thing in IFERROR, which would also hide genuine errors.
- Bands listed in the wrong direction
- =IFS(A2>=70, "C", A2>=80, "B", A2>=90, "A") labels every score of 70 or more as C, because the first test always wins. Nothing errors; the results just look plausible and are wrong. Order >= tests from the highest threshold down, and <= tests from the lowest up.
- An error in a later test poisons the whole formula
- =IFS(A2>0, "Positive", B2/C2>1, "Ratio high", TRUE, "Other") returns #DIV/0! when C2 is 0, even on rows where A2 was positive and the first pair should have answered. Excel evaluates the arguments before IFS picks between them, so guard risky tests with IFERROR inside the pair.
- Odd number of arguments
- Delete a result and leave its test behind and Excel refuses the formula as you press Enter, reporting too few arguments. The missing piece is nearly always a result rather than a test - count the arguments in pairs from the left to find the gap.
- Opening the file in Excel 2016 or earlier
- IFS was added in Excel 2019. Older versions show the formula as _xlfn.IFS(...) and return #NAME?, and the cached value is lost on the first recalculation. If the workbook has to be shared with those versions, write the logic as nested IFs instead.
Frequently Asked Questions
#N/A. Unlike IF, IFS has no value_if_false slot to fall back on. Add TRUE as the final test paired with whatever default you want, for example TRUE, "Unclassified", and unmatched rows land there instead of erroring.
They are running Excel 2016 or earlier, which does not have the function. The _xlfn prefix is how Excel stores a function name it does not recognise. Either move the workbook to Excel 2019 or later, or rewrite the logic as a nested IF chain, which works in every version.
127 pairs, which is 254 arguments. Long before that limit, a mapping that has grown past about eight branches is usually better kept as a two-column table on the sheet and read with XLOOKUP or VLOOKUP, so it can be edited without touching a formula.
Yes. The tests are independent expressions and can reference anything, including AND and OR combinations: =IFS(AND(B2="West", C2>1000), "Key account", C2>1000, "Large", TRUE, "Standard"). Only the order of the pairs links them together.
Speed is indistinguishable at any realistic scale; both stop mattering long before recalculation does. IFS is safer to edit, because every branch sits at the same level and there is no closing-bracket run to get wrong. Nested IF keeps two advantages: it works in every Excel version, and it has a real else branch.
Related Tools
IF Formula Generator
The two-outcome version, and the fallback when a file has to open in older Excel.
SWITCH & CHOOSE Generator
Better than IFS when you are matching one value against a list of exact cases.
IF AND Generator
For combining several conditions inside a single IFS test.
Excel Formula Explainer
Break a long test/result chain apart to see which pair is firing.