IFS function (multiple conditions) in Excel - Step-by-Step Guide

Introduction

The IFS function helps you check many conditions one by one and gives a result for the first true condition. It is useful when you want to avoid many nested IF statements and keep your formulas simple and easy to read.

When you want to assign letter grades based on numeric scores, like A, B, C, etc.

When you need to categorize sales amounts into different levels like Low, Medium, High.

When you want to check multiple conditions for a product status and return different messages.

When you want to calculate discounts based on purchase quantity ranges.

When you want to replace multiple IF formulas with a cleaner, easier-to-manage formula.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

You start entering the IFS formula

💡 Start with =IFS(

Step 3: Enter

- formula bar

You add the first condition and its result, for example: A1>90, "Excellent"

💡 Each condition is followed by the result if true, separated by commas

Step 4: Add

- formula bar

You add more condition-result pairs separated by commas, like A1>75, "Good", A1>50, "Average"

💡 Order conditions from most specific to least specific

Step 5: Close

- formula bar

You finish the formula with a closing parenthesis ) and press Enter

💡 The formula looks like =IFS(A1>90,"Excellent",A1>75,"Good",A1>50,"Average",TRUE,"Poor")

Step 6: Check

- the result cell

The cell shows the result based on the first true condition

Before vs After

Before

Cell A1 contains a score like 82, and no formula is applied

After

Cell B1 shows "Good" because 82 is greater than 75 but not greater than 90 using the formula =IFS(A1>90,"Excellent",A1>75,"Good",A1>50,"Average",TRUE,"Poor")

Settings Reference

Logical_test and value_if_true pairs

📍 Inside the IFS formula in the formula bar

To define conditions to check and what to return when a condition is true

Default: No default, you must provide at least one condition and result

Default catch-all condition

📍 Inside the IFS formula as the last condition

To return a default result if none of the previous conditions are true

Default: Not required but recommended

Common Mistakes

Not including a default TRUE condition at the end

If none of the conditions are true, the formula returns an error

Add TRUE as the last condition with a default result, like TRUE, "Other"

Using overlapping conditions in wrong order

The formula stops at the first true condition, so order matters

Order conditions from most specific (highest value) to least specific (lowest value)

Forgetting to separate condition-result pairs with commas

The formula will show a syntax error

Make sure each condition and its result are separated by commas

Summary

IFS checks multiple conditions in order and returns the result for the first true one.

It simplifies formulas by avoiding many nested IF statements.

Always include a default TRUE condition to handle unexpected cases.

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