Function IF() with Excel



Asst. Prof. Dr. Somsak Chanaim

International College of Digital Innovation, CMU

June 20, 2026

In Excel, the IF function can be used in various ways depending on the objective. The logic is based on conditional operators, which can be grouped into three main forms as shown below:

Symbol Meaning Example (Formula) Result
= Equal to =A1=10 TRUE / FALSE
<> Not equal to =A1<>10 TRUE / FALSE
> Greater than =A1>10 TRUE / FALSE
< Less than =A1<10 TRUE / FALSE
>= Greater than or equal to =A1>=10 TRUE / FALSE
<= Less than or equal to =A1<=10 TRUE / FALSE

1. Basic IF Function

Used to test a condition and return one of two results (TRUE or FALSE).

Syntax:

=IF(condition, value_if_true, value_if_false)

Example:

=IF(B2>10, "Greater than 10", "Less than or equal to 10")

Sample Data Table (10 People)

Copy and paste the table below into Excel starting at cell A1.

Name Exam Score Age Status
John 85 20 –
Emily 45 22 –
Sophia 55 18 –
Michael 90 21 –
Daniel 40 19 –
Olivia 60 24 –
Ethan 78 23 –
Lucas 30 17 –
William 95 25 –
Isabella 50 19 –

Example: Basic Usage of the IF Function

Check whether the exam score is “Pass” or “Fail” (Passing score is 50).

Formula:

=IF(A2>=50, "Pass", "Fail")

Result in the “Status” Column:

Status
Pass
Fail
Pass
Pass
Fail
Pass
Pass
Fail
Pass
Pass

2. Nested IF Function

Used when there are multiple conditions and you want to return multiple outcomes.

Syntax:

=IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false))

Example:

=IF(B2>80, "Excellent", IF(B2>=50, "Pass", "Fail"))

Example: Using the Nested IF Function

Assign grades based on exam score:

  • Greater than 80: "A"

  • Between 50–80: "B"

  • Less than 50: "F"

Formula:

=IF(B2>80, "A", IF(B2>=50, "B", "F"))

Result in the “Status” column:

Status
A
F
B
A
F
B
B
F
A
B

3. IF Function Combined with Other Functions

Used in combination with other functions (such as AND, OR, or calculations) to create more complex logic.

Syntax:

=IF(AND(condition1, condition2), value_if_true, value_if_false)

or

=IF(OR(condition1, condition2), value_if_true, value_if_false)


Examples:

=IF(AND(B2>10, C1<20), "Condition met", "Condition not met")

or

=IF(OR(B2="Yes", C1="No"), "Correct choice", "Incorrect choice")

Example: Using Nested IF with AND

Check if the exam score is passing AND age is over 20

Formula:

=IF(AND(B2>=50, C2>20), "Pass and over 20", "Does not meet criteria")

Result in the “Status” column:

Status
Does not meet criteria
Pass and over 20
Does not meet criteria
Pass and over 20
Does not meet criteria
Pass and over 20
Pass and over 20
Does not meet criteria
Pass and over 20
Does not meet criteria

Example: Using Nested IF with OR

Check if the exam score is passing OR age is over 20

Formula:

=IF(OR(B2>=50, C2>20), "Pass or over 20", "Does not meet criteria")

Result in the “Status” column:

Status
Pass or over 20
Pass or over 20
Pass or over 20
Pass or over 20
Does not meet criteria
Pass or over 20
Pass or over 20
Does not meet criteria
Pass or over 20
Pass or over 20

IF-like Function: IFS

IFS: Used to check multiple conditions, similar to nested IF, but in a simpler and more readable form.

=IFS(B2>80, "Excellent", B2>=50, "Pass", TRUE, "Fail")

Example: Using the IFS Function

Assigning Score Levels

Criteria:

  • Greater than 80: "Excellent"

  • Between 50–80: "Good"

  • Less than 50: "Needs Improvement"

Formula:

=IFS(B2>80, "Excellent", B2>=50, "Good", TRUE, "Needs Improvement")

Result in the “Status” column:

Status
Excellent
Needs Improvement
Good
Excellent
Needs Improvement
Good
Good
Needs Improvement
Excellent
Good

IF-like Function: SWITCH

SWITCH: Used when you have multiple specific values to compare against (ideal for constant value comparisons).

=SWITCH(A2, 1, "One", 2, "Two", "Other")

Example: Using the SWITCH Function

In this example, we assign letter grades based on exam scores:

  • Score greater than 80: “A”

  • Score between 50 and 80: “B”

  • Score less than 50: “F”

How to Use SWITCH

Since SWITCH cannot directly evaluate comparison operators (such as greater than or less than),
you need to convert conditions into constant values using another function like TRUE to handle range logic.

Formula:

=SWITCH(TRUE, B2>80, "A", B2>=50, "B", TRUE, "F")

Explanation of the Formula

  1. SWITCH(TRUE, ...): Allows SWITCH to behave like IF, checking conditions one by one

  2. B2>80: If the score is greater than 80, return "A"

  3. B2>=50: If the score is between 50 and 80, return "B"

  4. TRUE: Catch-all fallback condition — if none of the above are met, return "F"

Result from the formula in the “Status” column

Name Exam Score Status
John 85 A
Emily 45 F
Sophia 55 B
Michael 90 A
Daniel 40 F
Olivia 60 B
Ethan 78 B
Lucas 30 F
William 95 A
Isabella 50 B

Advantages of SWITCH

  1. Easier to read than Nested IF or IFS when dealing with multiple conditions.

  2. More concise structure for constant values or simple logical expressions.

Exercise

Use the following employee data to answer the questions.

Copy the table and paste it into cell A1 in Excel.

Employee Position Salary Evaluation Score
A1 Supervisor 70000 100
A2 Staff 80000 65
A3 Manager 70000 95
A4 Staff 85000 55
A5 Manager 65000 85
A6 Manager 50000 50
A7 Supervisor 40000 95
A8 Staff 35000 75
A9 Supervisor 45000 50
A10 Supervisor 35000 100
A11 Supervisor 80000 65
A12 Supervisor 95000 55
A13 Staff 45000 65
A14 Staff 90000 75
A15 Supervisor 30000 95
A16 Manager 45000 95
A17 Staff 95000 85
A18 Supervisor 65000 60
A19 Staff 40000 65
A20 Manager 70000 85

1. Use the IF() function to determine who gets a bonus.

Condition:

  • If score ≥ 75 → gets a bonus

  • If score < 75 → no bonus

2. Use the IFS() function to assign bonus percentages based on score.

Condition:

  • Score ≥ 90 → 15% bonus

  • Score ≥ 80 → 10% bonus

  • Score ≥ 70 → 5% bonus

  • Below 70 → no bonus

3. Use nested IF() to assign bonus based on both salary and score.

Condition:

  • If salary < 50,000 and score ≥ 80 → 10% bonus

  • If salary ≥ 50,000 and score ≥ 90 → 5% bonus

  • Otherwise → no bonus

4. Use SWITCH() to convert position into level.

Condition:

  • “Staff” → Level 1

  • “Supervisor” → Level 2

  • “Manager” → Level 3

Answer Key

1)

=IF(D2>=75, "Bonus", "No Bonus")

2)

=IFS(D2>=90, "15%", D2>=80, "10%", D2>=70, "5%", D2<70, "0%")

3)

=IF(B2<50000, IF(D2>=80, "10%", "0%"), IF(D2>=90, "5%", "0%"))

4)

=SWITCH(B2, "Staff", 1, "Supervisor", 2, "Manager", 3, "Unknown")