IFS function (multiple conditions) in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IFS function do in Excel?

The IFS function checks multiple conditions one by one and returns the value for the first condition that is TRUE. It helps avoid nested IF statements.

beginner

Write the syntax of the IFS function.

IFS(condition1, value_if_true1, condition2, value_if_true2, ..., conditionN, value_if_trueN)

Each condition is tested in order. The function returns the value for the first TRUE condition.

intermediate

How does IFS differ from nested IF statements?

IFS is simpler and cleaner because you list conditions and results in pairs without many parentheses. Nested IFs require multiple IF functions inside each other, which can be confusing.

intermediate

What happens if none of the IFS conditions are TRUE?

If no condition is TRUE, IFS returns a #N/A error unless you add a final condition that is always TRUE (like TRUE) with a default value.

beginner

Example: How to use IFS to assign grades based on scores?
Score >= 90: "A"
Score >= 80: "B"
Score >= 70: "C"
Score < 70: "F"

Use this formula:
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

This checks each condition in order and returns the matching grade.

What will the formula =IFS(A1>10, "High", A1>5, "Medium", TRUE, "Low") return if A1 is 7?

A "Medium"

B "High"

C "Low"

D #N/A error

Since 7 is not greater than 10, the first condition is FALSE. The second condition (A1>5) is TRUE, so it returns "Medium".

Which of these is a correct way to write an IFS function?

A =IFS(A1>10, "Yes", A1<5)

B =IFS(A1>10, "Yes", A1<5, "No")

C =IFS(A1>10 "Yes", A1<5, "No")

D =IFS(A1>10, "Yes", A1<5, "No",)

Each condition must be followed by a value. Option B correctly pairs conditions and values.

What error does IFS return if no conditions are TRUE and no default is given?

A #VALUE!

B #REF!

C #N/A

D #DIV/0!

IFS returns #N/A error if no condition is TRUE and no default condition is provided.

Why might you use IFS instead of nested IFs?

A IFS is faster to calculate

B IFS allows multiple results in one cell

C IFS works only with numbers

D IFS is easier to read and write for multiple conditions

IFS simplifies writing multiple conditions without complex nesting, making formulas easier to read.

In the formula =IFS(A1>90, "A", A1>80, "B", TRUE, "C"), what does TRUE represent?

A A default catch-all condition

B A condition that is always FALSE

C A syntax error

D A reference to cell TRUE

TRUE as a condition always evaluates to TRUE, so it acts as a default if earlier conditions fail.

Explain how the IFS function works and how it can replace nested IF statements.

Think about checking conditions one by one and stopping at the first match.

Describe how to handle a situation where none of the IFS conditions are met.

Use a condition that always returns TRUE at the end.

Practice

1. What does the IFS function do in Excel?

easy

A. Tests multiple conditions and returns the value for the first true one.

B. Adds numbers in a range.

C. Finds the average of numbers.

D. Sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of IFS

   The IFS function checks several conditions one by one.

2. Step 2: Identify the output behavior

   It returns the value linked to the first condition that is true.

3. Final Answer:

   Tests multiple conditions and returns the value for the first true one. -> Option A

4. Quick Check:

   IFS returns first true condition value [OK]

Hint: Remember: IFS stops at first true condition [OK]

Common Mistakes:

• Thinking IFS sums values
• Confusing IFS with AVERAGE
• Assuming IFS sorts data

2. Which of these is the correct syntax for the IFS function in Excel?

easy

A. =IFS(condition1; value1; condition2; value2)

B. =IFS(value1, condition1, value2, condition2)

C. =IFS(condition1, value1, condition2, value2, TRUE, default_value)

D. =IFS(condition1 value1 condition2 value2)

Solution

1. Step 1: Recall IFS syntax rules

   IFS requires pairs: condition, then value, separated by commas.

2. Step 2: Check each option's format

   =IFS(condition1, value1, condition2, value2, TRUE, default_value) uses correct comma separators and pairs conditions with values properly.

3. Final Answer:

   =IFS(condition1, value1, condition2, value2, TRUE, default_value) -> Option C

4. Quick Check:

   Correct IFS syntax uses condition-value pairs separated by commas [OK]

Hint: Pairs: condition then value, separated by commas [OK]

Common Mistakes:

• Using semicolons instead of commas
• Swapping condition and value order
• Missing commas between pairs

3. Given the formula =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") and cell A1 contains 85, what will be the result?

medium

A. "A"

B. "F"

C. "C"

D. "B"

Solution

1. Step 1: Evaluate conditions in order with A1=85

   Check if 85 > 90 (false), then 85 > 80 (true).

2. Step 2: Return value for first true condition

   Since 85 > 80 is true, return "B" immediately.

3. Final Answer:

   "B" -> Option D

4. Quick Check:

   First true condition value is "B" [OK]

Hint: Check conditions top to bottom, stop at first true [OK]

Common Mistakes:

• Choosing "A" because 85 is close to 90
• Ignoring order of conditions
• Selecting default "F" incorrectly

4. Identify the error in this formula: =IFS(A1>90, "A", A1>80, "B", A1>70, "C") when A1 is 65.

medium

A. Too many conditions for IFS function.

B. Missing a default TRUE condition for fallback value.

C. Conditions are not in descending order.

D. Incorrect use of quotation marks around letters.

Solution

1. Step 1: Check what happens if no condition is true

   With A1=65, none of the conditions (A1>90, A1>80, A1>70) are true.

2. Step 2: Identify missing fallback condition

   Without a final TRUE condition, IFS returns #N/A error when no conditions match.

3. Final Answer:

   Missing a default TRUE condition for fallback value. -> Option B

4. Quick Check:

   Always include TRUE fallback to avoid errors [OK]

Hint: Always end IFS with TRUE for default value [OK]

Common Mistakes:

• Forgetting default TRUE condition
• Thinking order of conditions causes error
• Assuming quotation marks cause error

5. You want to assign grades based on scores in cell B2 using IFS: 90+ = "Excellent", 80-89 = "Good", 70-79 = "Average", below 70 = "Poor". Which formula correctly implements this?

hard

A. =IFS(B2>=90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor")

B. =IFS(B2>90, "Excellent", B2>80, "Good", B2>70, "Average", TRUE, "Poor")

C. =IFS(B2>=90, "Excellent", B2>80, "Good", B2>70, "Average", TRUE, "Poor")

D. =IFS(B2>90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor")

Solution

1. Step 1: Understand grade ranges and conditions

   Grades include 90 and above as "Excellent", 80 to 89 as "Good", 70 to 79 as "Average", below 70 as "Poor".

2. Step 2: Check condition operators for correct ranges

   Use >= for lower bounds to include boundary values correctly.

3. Step 3: Verify fallback condition

   TRUE at the end covers scores below 70 with "Poor".

4. Final Answer:

   =IFS(B2>=90, "Excellent", B2>=80, "Good", B2>=70, "Average", TRUE, "Poor") -> Option A

5. Quick Check:

   Use >= for inclusive ranges and TRUE fallback [OK]

Hint: Use >= for boundaries and TRUE last for default [OK]

Common Mistakes:

• Using > instead of >= causing boundary errors
• Missing TRUE fallback condition
• Incorrect order of conditions