|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

IFS

IFS

  • Last updated on

Description

Use this function to check multiple conditions and return a value corresponding to the first TRUE condition without nesting multiple IF statements. Can be used with CHILDREFS.

 

Syntax

IFS(logical_test1, value_if_true1, […, logical_test_127, value_if_true_127])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
logical_test_1 Yes The first condition to evaluate. Any value or expression that can be evaluated to TRUE or FALSE.
value_if_true_1 Yes The value to return if logical_test_1 is TRUE. Any value, including text, numbers, or logical values.
logical_test_n,
value_if_true_n		 Optional Additional pairs of conditions and results.  Same criteria as logical_test_1 and value_if_true_1.

 

Examples

Sample data

  A B
1 Score Grade
2 95 A
3 84 Very Good
4 75 Good
5   No Score
6 60 Fail

Sample formulas

Use case Formula Explanation and Result
Assign a letter grade to a score using a series of conditions. =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")

This formula works in the following manner:

  • IFS(: This is the function itself, which checks multiple conditions in sequence.
  • A2>=90,"A": The first test checks if A2 (95) is >= 90. This is TRUE, so the formula returns "A" and stops.
  • A2>=80,"B": This test is not evaluated.
  • A2>=70,"C": This test is not evaluated.
  • A2>=60,"D": This test is not evaluated.
  • TRUE,"F": This "catch-all" test is not evaluated.

For this data set this formula returns "A".
Provide a descriptive evaluation based on a score using a set of tiered conditions. =IFS(A3>95,"Excellent",A3>82,"Very Good",A3>75,"Good",A3>65,"Satisfactory",TRUE,"Needs Improvement")

This formula works in the following manner:

  • IFS(: This is the function itself.
  • A3>95,"Excellent": Checks if A3 (84) is > 95. This is FALSE.
  • A3>82,"Very Good": Checks if A3 (84) is > 82. This is TRUE, so the formula returns "Very Good" and stops.
  • The remaining tests are not evaluated.

For this data set, the formula returns "Very Good".
Assign a detailed letter or descriptive grade including an "Outstanding" or "Perfect" category. =IFS(A4=100,"Perfect",A4>=90,"Outstanding",A4>=80,"Great",A4>=70,"Good",A4>=60,"Pass",TRUE,"Fail")

This formula works in the following manner:

  • IFS(: This is the function itself.
  • A4=100,"Perfect": Checks if A4 (75) is = 100. This is FALSE.
  • A4>=90,"Outstanding": Checks if A4 (75) is >= 90. This is FALSE.
  • A4>=80,"Great": Checks if A4 (75) is >= 80. This is FALSE.
  • A4>=70,"Good": Checks if A4 (75) is >= 70. This is TRUE, so the formula returns "Good" and stops.
  • The remaining tests are not evaluated.

For this data set, the formula returns "Good".
Provide a basic pass/fail grade based on the provided value, while handling a blank cell and invalid scores. =IFS(ISBLANK(A5),"No Score",A5>100,"Invalid",A5>=70,"Pass",TRUE,"Fail")

This formula works in the following manner:

  • IFS(: This is the function itself.
  • ISBLANK(A5),"No Score": Checks if A5 is blank. This is TRUE, so the formula returns "No Score" and stops.
  • The remaining tests are not evaluated.

For this data set, the formula returns "No Score".
Provide a basic pass/fail grade based on the provided value, while handling a blank cell and invalid scores. =IFS(ISBLANK(A6),"No Score",A6>100,"Invalid",A6>=70,"Pass",TRUE,"Fail")

This formula works in the following manner:

  • IFS(: This is the function itself.
  • ISBLANK(A6),"No Score": Checks if A6 (60) is blank. This is FALSE.
  • A6>100,"Invalid": Checks if A6 (60) is > 100. This is FALSE.
  • A6>=70,"Pass": Checks if A6 (60) is >= 70. This is FALSE.
  • TRUE,"Fail": This is the final "catch-all" condition. Since it is TRUE, the formula returns "Fail" and stops.

For this data set, the formula returns "Fail".

 

Notes

  • IFS evaluates conditions in order and returns the value corresponding to the first TRUE condition.
  • If no conditions are TRUE and there's no final TRUE condition, IFS returns a #N/A error.
  • IFS can handle up to 127 condition/value pairs.
  • IFS is more efficient and readable than nested IF statements for multiple conditions.

 

Tips

  • Always include a final TRUE condition to handle cases where no other conditions are met.
  • Use IFS instead of nested IF statements when you have more than two conditions to check.
  • Ensure your conditions are mutually exclusive to avoid unexpected results.
  • For complex logical tests, you can use the AND, OR, and NOT functions within the IFS conditions.
  • When working with large datasets, consider using  VLOOKUP or INDEX/MATCH for better performance.

 

Related functions

AND
CHOOSE
IF
IFERROR
IFNA
IFS
NOT
OR

Was this article helpful?
0 out of 1 found this helpful
Description Use this function to check multiple conditions and return a value corresponding to the first TRUE conditi...