Excel IFS Default Value: Set a Reliable Fallback Result

Formulas & functions · Updated November 18, 2026

Excel’s IFS function has no built-in default argument: if none of its tests is TRUE, it returns #N/A. Add a fallback by making the final test TRUE; with a score in B2, use =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"Needs review").

What the IFS function does

IFS evaluates a series of logical tests and returns the value paired with the first test that evaluates to TRUE. It is useful when a result depends on several conditions, such as assigning a grade, categorizing an order, or labeling a measurement. Compared with a long chain of nested IF functions, IFS can make these rules easier to scan.

Its syntax is IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],…). Each test must be followed by the result to return when that test is true. The square brackets in Excel’s syntax notation mean later test-and-result pairs are optional; they are not characters to type into a formula. IFS accepts up to 127 test-and-result pairs.

There is no separate value_if_false argument at the end, unlike IF. That difference explains the default-value issue: if every test is false, IFS has no result to return and produces #N/A.

How to add a default result

Use the logical constant TRUE as the final test, then put your fallback value after it. Because TRUE is always true, it matches when execution reaches that last pair—that is, when all preceding tests were false. This makes it a catch-all without changing the earlier conditions.

For example, with the student’s numeric score in B2, the formula in the opening paragraph returns A for 90 or higher, B for 80 through 89, C for 70 through 79, and Needs review for anything lower. A score of 85 returns B because that is the first matching test. A score of 62 reaches the final TRUE and returns Needs review.

TRUE is a logical value, not text. Do not put quotation marks around the final test: TRUE is always true, while "TRUE" is a text string and is not a catch-all test. You can choose a text, number, or blank-looking result for the fallback, depending on what makes sense in your sheet.

Choosing the right fallback

A fallback should communicate what the formula means when none of the stated rules applies. It is not merely a way to hide an error. Choose one that helps someone interpret or act on the result:

  • A clear label: Use a result such as Needs review or Unclassified when a person should check the input or add a missing category.
  • A blank-looking result: Use an empty text string when no visible output is preferable. With a score in B2, the formula =IFS(B2>=90,"A",B2>=80,"B",TRUE,"") displays nothing for a score below 80. The cell still contains a formula; it is not truly empty.
  • A numeric result: Return 0 only if zero is a meaningful value in your reporting. Otherwise it can be mistaken for a real calculated result.
  • An explicit error or warning label: A message such as Check input can make unexpected values more visible than a blank.

For a blank-looking fallback in that example, the assumed layout is a score in B2. The formula returns an empty text string for scores below 80; it still assigns A for 90 or above and B for 80 through 89. Be especially careful if another formula counts, tests, or charts these results: an empty string is text returned by a formula, not an unused cell.

Put conditions in the right order

IFS returns the result for the first TRUE test, so condition order matters whenever tests overlap. In the grade example, B2>=70 is also true for a score of 95. Placing that test first would label a 95 as C before Excel could reach the A condition. Put narrower or higher-priority rules ahead of broader ones, and reserve the final TRUE for the fallback.

For ranges, a useful pattern is to list descending upper thresholds or ascending lower thresholds so each boundary is easy to check. With a score in B2, the tests B2>=90, B2>=80, and B2>=70 cover the intended bands because the higher thresholds come first. Test values at the edges—such as 69, 70, 79, 80, 89, and 90—to confirm the result changes where you expect.

Sometimes a condition should be deliberately checked before numeric rules. For example, a blank score could otherwise be treated unexpectedly in a comparison. With a score in B2, put a blank check first: =IFS(B2="","Missing score",B2>=90,"A",B2>=80,"B",TRUE,"Below B"). This returns Missing score when B2 is blank or contains a formula returning an empty string. If those cases should be distinguished, test for them separately using a suitable approach for your worksheet.

Common problems and how to fix them

  • #N/A appears: No test matched and there is no final catch-all. Add a last pair such as TRUE,"Other", or decide that an unmatched value should remain an error.
  • The wrong category appears: An earlier condition may also be true. Reorder overlapping tests from most specific or highest priority to least specific, then verify the threshold boundaries.
  • A value falls through unexpectedly: Check the actual cell contents and data type. A number stored as text may not behave like a numeric value in comparisons; spaces or unexpected text can also prevent a test from matching as intended.
  • A blank seems to receive a category: Blank cells can behave differently from text and numbers in comparisons. If blanks have a special meaning, test for them explicitly before the other rules.
  • The formula is rejected: Check that every logical test has a corresponding result and that commas, parentheses, and quotation marks are paired correctly. Some regional Excel settings use semicolons instead of commas as argument separators; the examples here use commas.

When debugging, temporarily check the inputs and the order of the conditions rather than replacing every error with a blank. A broad fallback can make a formula look tidy while concealing misspelled labels, incomplete categories, or unexpected source data.

IFS compared with IF, SWITCH, and IFERROR

Nested IF can express the same kind of branching and includes an explicit result for the false case. With a score in B2, =IF(B2>=90,"A",IF(B2>=80,"B","Other")) returns Other when the score is below 80. This is a reasonable alternative for a small number of rules, although a long chain of nested IF functions can be harder to read and edit.

SWITCH is more suitable when one expression is compared with a list of exact values, such as mapping status codes to labels. It is not a direct substitute for ordered range tests such as score thresholds. IFS is often a clearer choice for multiple different logical tests, while SWITCH can be more compact for exact-match cases.

IFERROR solves a different problem. It returns an alternative when an expression produces an error; it does not provide IFS with a missing logical fallback. Wrapping IFS in IFERROR may hide #N/A, but it can also hide other errors that should be investigated. If the intended rule is simply to return Other when no condition matches, a final TRUE pair states that rule more directly.

Excel versions and compatibility

IFS is available in Microsoft Excel 2019 and later, including Microsoft 365, Excel 2021, and Excel 2024. It is also supported in current Excel for Mac and Excel for the web. Older Excel releases, such as Excel 2016, may not recognize IFS and can show #NAME? instead. If a workbook must work in an older version, nested IF is a more compatible option.

The formula logic is the same in Excel for Microsoft 365 on Windows, Mac, and the web. Regional settings can affect which character separates arguments, so a workbook configured for semicolon separators may require semicolons in place of the commas shown here. The function name and the need for a final catch-all test do not change.

Frequently Asked Questions

What does IFS return if no condition is true?
It returns #N/A when none of its logical tests evaluates to TRUE and the formula has no matching result. Add a final TRUE test with the value you want as the fallback.

Can I use a blank as the IFS default?
Yes. Put an empty text string, written as "", after a final TRUE test. The cell will look blank, but it contains a formula and returns text rather than being genuinely empty.

Does IFS have an ELSE argument?
No. Unlike IF, IFS has no dedicated final false-result argument. A final TRUE test paired with a result performs the usual catch-all role.

Can I use IFERROR to set the IFS default?
You can use IFERROR to replace an error result, but it is not the clearest way to describe an unmatched case and may suppress errors unrelated to missing conditions. Use a final TRUE pair when you want a deliberate default for all remaining inputs.

Why does my IFS formula return the wrong result?
IFS uses the first TRUE test, so overlapping conditions may match earlier than you intended. Reorder the tests, check whether the source values are numbers or text, and verify cases at each threshold boundary.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.