Skip to Content

How to use Excel IF Function (With Examples)

The Excel IF function lets you perform a logical test and display a desired value. You can display one value if the condition is true or another value if the condition is false.

For example, you can identify if the sales rep could get more than a $10K incentive in the given month. We are assuming that the incentive is mentioned in column C.

=IF(C2>=10000, "YES", "No")

Where,

  • C2>=10000 is the logical test to check if the value in the cell C1 is greater than or equal to 10,000
  • Yes is the desired output if the condition is met
  • No is the desired output if the condition is not met

Syntax

The IF is a simple logical function in Excel. It has the following syntax.

=IF(logical_test, [value_if_true], [value_if_false])

Where,

  • logical_test is the first argument, where you can define the condition to be tested
  • value_if_true argument lets you display the desired output if the condition is met
  • value_if_false argument lets you display the desired output if the condition is not met

You can use the following comparison operators to define conditions or criteria.

  • “=” – Equal To
  • “<“- Less Than
  • “<=” – Less Than or Equal To
  • “>” – Greater Than
  • “>=” – Greater Than or Equal To
  • “<>” – Not Equal To

Important Notes:

  • The function is not case-sensitive
  • All text values must be enclosed within double quotes
  • The function does not support wildcards

To learn the difference between the IF and IFS functions in Excel, click here.

How to use the IF Function in Excel

Download the example spreadsheet used to demonstrate the IF function in the upcoming section. Practice is the key to success!

Download Now

Example 1: IF Function Basics

Our task is to find the students who could score 40+ marks in the given exam. We will mark them as Passed or Failed based on their scores.

  • Passed if they obtained more than or equal to 40 marks
  • Failed if they obtained less than 40 marks

Refer to the following image,

How to Use Excel IF function (With Examples) - Syntax Explained

Here are the steps,

  • Select the cell C2
  • Type =IF
  • Select the first option from the popup
  • Specify the first condition as B2>=40
  • Type , on your keyboard
  • Specify the value “Passed” to be displayed if the condition is met
    (Make sure to use the double quotation marks as this is a text value)
  • Type , on your keyboard
  • Specify the value “Failed” to be displayed if the student scored less than 40 marks
    (Make sure to use the double quotation marks as this is a text value)
  • Complete the bracket using ) on your keyword
  • Press the Enter key

Our final formula is as follows,

=IF(B2>=40,"PASSED","FAILED")

Where,

  • B2>=40 is the logical condition that checks if the value in the cell B2 is greater than or equal to 40
  • Passed is the desired output if the condition is true
  • No is the desired output if the condition is false

The results are displayed in the following image,

Example 2: Combining IF with AND function in Excel

Suppose you plan to offer a bonus to employees with an 8+ performance rating out of 10. The bonus is applicable only to those who have worked for a minimum of two years for your company.

Refer to the following data.

Here are the steps,

  • Click on the cell D2
  • Type =IF
  • Select the first option from the popup
  • Type AND
    (As AND is a function in Excel, you will see the following popup)
  • Double-click on the AND command
  • Specify the first condition as B2>=8
  • Type ,
  • Specify the next condition as C2>=2
  • Complete the bracket for the AND function using )
  • Type ,
  • Specify the value “Yes” if the logical test is TRUE
    (Make sure to use the double quotation marks as this is a text value)
  • Type ,
  • Specify the value “No” if the logical test is FALSE
    (Make sure to use the double quotation marks as this is a text value)
  • Complete the bracket for the IF function using )
  • Press the Enter key

Our final formula is as follows,

=IF(AND(B2>=8,C2>=2),"YES","No")

Where,

  • B2=>8 is the condition to find the employees with performance ratings equal to greater than 8
  • C2=> is the condition to find the employees who have been associated with the company for more than 2 years
  • Eligible is the desired value to be returned if the employee satisfies the given conditions
  • Not Eligible is the desired value to be returned if the employee doesn’t satisfy the given condition

The results are displayed in the following image,

Example 3: Combining IF with OR function in Excel

Suppose that we are planning to launch a black friday sale. The campaign focuses on boosting the sales of products that were not sold in the last month. We will offer discounts on the products whose:

  • Zero quantities were sold in the last month
  • Price is above $100

Refer to the following data.

Here are the steps,

  • Click on the cell D2
  • Type =IF
  • Select the first option from the popup
  • Type OR
    (As OR is a function in Excel, you will see the following popup)
  • Double-click on the OR command
  • Specify the first condition as B2=0
  • Type ,
  • Specify the next condition as C2>=100
  • Complete the bracket for the OR function using )
  • Type ,
  • Specify the value “Eligible” if the logical test is TRUE
    (Make sure to use the double quotation marks as this is a text value)
  • Type ,
  • Specify the value “Not Eligible” if the logical test is FALSE
    (Make sure to use the double quotation marks as this is a text value)
  • Complete the bracket for the IF function using )
  • Press Enter key

Our final formula is as follows,

=IF(OR(B2=0, C2>=100), "Eligible", "Not Eligible")

Where,

  • B2=0 is used to find the products that were not sold in last month
  • C2=>100 is used to find products with price greater than $100
  • Eligible is the desired value to be returned if either of the conditions is satisfied
  • Not Eligible is the desired value to be returned if both of the conditions are not satisfied

The results are displayed in the following image,

Example 4: Combining IF with AND and OR functions in Excel

We have city-wise sales data as shown in the following image. Our task is to identify the employees who could make more than $100,000 in sales in Phoenix or Miami.

Here are the steps,

  • Select the cell D2
  • Type =IF
  • Select the first option from the popup
  • Type AND
    (As the AND is a function in Excel, you will see the following popup)
  • Double-click on the AND command from the popup
  • Next, type OR
  • Double-click on the OR command from the popup
  • Specify the first condition B2=”Phoenix”
  • Type ,
  • Specify the second condition B2=”Miami”
  • Press ) to complete the bracket for the OR function
  • Type ,
  • Specify the third condition C2>=100000
  • Complete the bracket ) for the AND function
  • Specify the value “Yes” to be displayed if the employee could hit the sales target in the either Phoenix or Maimi
    (Make sure to use the double quotation marks as this is a text value)
  • Type ,
  • Specify the value “No” to be displayed if the employee failed to hit the sales target
  • Complete the bracket for the IF function )
  • Press the Enter key on your keyboard

Our final formula is as follows,

=IF(AND(OR(B2="Phoenix", B2="Miami"), C2>=100000), "Yes", "No")

Where,

  • OR(B2=”Phoenix”, B2=”Miami”) ensures the sales were made in the target cities
  • AND(OR(B2=”Phoenix”, B2=”Miami”), C2>=100000) identifies if the employee made more than $100,000 in sales in the target cities

The results are displayed in the following image,

Example 5: Inserting the formula in place of the value_if_true argument

Let us consider the previous example only. Our task is to find and reward the employees who could hit the sales target of $100,000 in Phoenix or Miami. We will offer them a 10% bonus on whatever sales they made in the target cities.

In this case, we need to multiply the sales by 0.1 to find the 10%.

Bonus = Sales x 0.1

Refer to the following image.

Here are the steps,

  • Select the cell D2
  • Copy and paste AND(OR(B2=”Phoenix”,B2=”Miami”),C2>=100000)
    (It is the condition we created using the AND and OR functions in the previous example. It identifies the employees who could hit the sales target in either Phoenix or Miami.)
  • Type , on your keyboard to move to the next argument of the IF function
  • Next, specify the value C2*0.1
    (This is a formula that will return a bonus amount if the condition is met)
  • Type ,
  • Specify the value “Better Luck Next Time” if the condition is not met
    (Make sure to use the double quotation marks as this is a text value)
  • Complete the bracket using )
  • Press the Enter key

Our final formula is as follows,

=IF(AND(OR(B2="Phoenix", B2="Miami"), C2>=100000), C2*0.1, "Better Luck Next Time")

Where,

  • AND(OR(B2=”Phoenix”, B2=”Miami”), C2>=100000) is the condition to find the employees who could achieve the sales target in either Phoenix or Miami
  • C2*0.1 is the formula to calculate and display the 10% bonus amount
  • Better Luck Next Time is the value to be returned if the employee didn’t receive any bonus

The results are displayed in the following image,

Difference Between the IF and IFS Function in Excel

They are both prebuilt logical functions in Excel. The IF function is used to test a single condition. But the IFS function lets you test multiple conditions simultaneously and return the value corresponding to the first TRUE condition.

For example, consider that you need to grade each student as:

  • Diamond: Scored 90+ marks
  • Gold: Scored 60+ marks
  • Bronze: Scored 40+ Marks
  • Failed: Score is below 40

Here is the example data,

Our formula would be:

=IFS(B2>=90, "Diamond", B2>=60, "Gold", B2>=40, "Bronze", B2<40, "Failed")

Where,

  • B2>=90 is the first condition to check if the student’s score is greater than or equal to 90
  • Diamond is the desired output if the student’s score is 90+
  • B2>=60 checks if the student’s score is greater than or equal to 60
  • Gold is the desired output if the second condition is true
  • B2>=40 checks if the student’s score is greater than or equal to 40
  • Bronze is the desired output if the third condition is true
  • B2<40 condition checks if the student failed the given exam

The output is displayed in the following image.

Note that the function allows you to test a maximum of 127 conditions. For each condition, you need to specify the value to be displayed if true. Here’s the IFS function syntax,

=IFS(logical_test1, Value1, [logical_test2, Value2] …, [logical_test127, Value127])

Where,

  • logical_test1, logical_test2, logical_test127… are the conditions
  • Value1, Value2, Value127… are the values to be displayed if the respective condition is met

If you are wondering, which value will be displayed if multiple conditions are met? The answer is the value corresponding to the first condition, which was true.

Click here to read our in-depth guide: How to use Excel IFS Function (With Examples)

Takeaway

Both IF and IFS are powerful functions in Excel.

You can combine the IF with other functions in Excel to create complex formulas. We have discussed the most common scenarios in the above section. If you have any specific scenario in mind where the IF function proves to be a powerful tool, please comment below.

Additional Resources:

  • Learn All Excel Logical Functions (With Examples)
  • AND Function in Excel
  • OR Function in Excel
  • NOT Function in Excel
  • XOR Function in Excel
  • Ultimate Guide to Comparison Operators in Excel
  • Excel Shortcuts List

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.