Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»How to Use the IF Function With 3 Conditions

    How to Use the IF Function With 3 Conditions

    Nisha GurungBy Nisha GurungJuly 21, 2023 Excel 5 Mins Read

    Excel’s IF function is extremely useful to test criteria and return the specified value when the value is True or False. I personally use this function on a day-to-day basis to return Yes/No, Completed/Pending, Good/Bad, and so on. But, its only drawback is you cannot test more than one criterion with the IF function.

    You’re lucky if the IFs function is available in your Excel version to test the multiple conditions. However, even if it isn’t, you could always nest the IF function with IF. Or, use IF with other logical functions such as AND and OR nested together.

    Arguments for the IF function

    Excel’s IF function takes up the condition and values to return as function arguments. 

    Syntax: IF(logical_test, value_if_true, [value_if_false])
    • logical_test: condition you want to test with TRUE or FALSE logic
    • value_if_true: the value you want to return when the logical test is TRUE
    • [Value_if_false]: the value you want to return when the logical test is FALSE

    Note that the arguments placed inside the [] brackets are optional. 

    How to Use the IF Function With 3 Conditions

    Method 1: Nest IF inside IF

    Firstly, you can actually nest the IF function inside IF. By nesting IF statements, you could create as many conditions to test as you want. To check 3 conditions, we will nest IF two times inside the IF function.

    Example: Suppose you have an Excel record of Name and Age. You need to check if they’re eligible, not eligible, or could be eligible. For this, you have three different conditions to test and return the value:

    • Return “No” if the value is less than the age criteria
    • Return “Yes” if the value is greater than the age criteria
    • Return “Maybe” if the value is equal to the age criteria

    To find out, use the formula given below.

    =IF(B2<$E$2, "No", IF(B2>$E$2, "Yes", IF(B2=$E$2, "Maybe")))

    As you can see in the formula, we have passed down three different criteria in the IF function. In our logical test, we’ve locked cell $E$2 to make it an absolute cell. This way the other cells will look only at the value of the E2 cell for logical comparison. 

    Since the value of cell B2 exactly matches with the E2 cell, it returned Maybe. While the cell is still selected, extend the flash-fill handle to return the values in other cells.

    Method 2: Nest AND Function inside IF function

    Similar to the IF function, AND function is also a logical function used to test the conditions. You could nest AND function inside the IF function to test three different conditions. Use this formula when all three conditions need to be either True or False.

    Syntax: IF(AND(logical1, [logical2] [logical3]), value_if_true, [value_if_false]))

    Example: Let’s assume you have the History test scores of Grade 2 students in your worksheet. You need to provide remarks to the students based on their test scores. According to the school’s standard remark rule, the conditions are:

    • Excellent: Scores greater than or equal to 80
    • Good: Scores greater than or equal to 70
    • Fair: Scores greater than or equal to 60

    Now, you can use this formula to evaluate the remarks of students

    =IF(AND(B6>=80),"Excellent",IF(AND(B6>=70),"Good",IF(AND(B6>=60),"Fair")))

    In the formula, IF(AND(B6>=80), “Excellent” is your first condition. IF(AND(B6>=70), “Good” is the second condition, and IF(AND(B6>=60),”Fair”) is third condition. 

    The AND function returns either TRUE or FALSE for each condition. Then, the IF function returns “Excellent” if the value is TRUE. Similarly, in case the value is FALSE, it returns either “Good” or “Fair.” In our case, it returned Good. Use the Flash-fill to return values in the remaining cells. 

    Method 3: Nest OR function inside IF function

    OR function is another logical function that returns TRUE or FALSE values whenever you test a certain condition. With IF and OR functions nested together, you could test three criteria for your data. This function is useful when one of the three conditions can be either True or False. 

    Syntax: IF(OR(Something is True, Something else is True), Value if True, Value if False)

    Example: You have a list of employees and their total sales of each item. Based on their sales performance, each employee will get a 2.5% commission from their total sales. There are three conditions you need to test to decide whether the employees should get a commission or not.

    • Sales of Mouse must be greater than or equal to 2000.
    • Sales of Keyboard must be greater than or equal to 2000.
    • Sales of TV must be greater than or equal to 2000.

    To test, enter the IF and OR formula in the box.

    =IF(OR(B6>=2000,C6>=2000,D6>=2000),E6*$B$3,"N/A")

    In the given formula, the OR function will first check whether the values of B6, C6, and D6 are greater than or equal to the Target. Then, it returns TRUE or FALSE based on all three conditions.

    Now, the IF function will return the Total sales *2.5 % commission if the value is True. Or, “N/A” if the value is False. Since it was true, the formula returned 148.725 commission in the cell.

    Drag down the flash-fill to return results in other cells too.

    Alternative to using IF function with multiple criteria

    If you have access to the IFs function, you do not have to go through the hassle of nesting several logical functions with IF. Excel’s IFs function is a new function that checks multiple criteria. It is available in Excel 2019 and later versions.

    Syntax: IFs(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3])

    The functionality of the IF function is pretty much similar to the IF function. You can pass down the criteria and value to return to when true or false. The only difference is you can enter multiple criteria which is up to 127 in maximum. 

    Excel Functions Formula
    Nisha Gurung

      As a Business Student, Nisha first used Excel to outline data trends for her college projects. Later, she started exploring basic Excel functions when she realized it is a powerful tool for all businesses today. Nisha believes she can relate to many of the issues users face when starting their Excel Journey. She uses this knowledge and experience to curate contents that are fit for all types of Excel users. Apart from writing, she enjoys reading books and traveling.

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Arguments for the IF function
      • How to Use the IF Function With 3 Conditions
        • Method 1: Nest IF inside IF
        • Method 2: Nest AND Function inside IF function
        • Method 3: Nest OR function inside IF function
      • Alternative to using IF function with multiple criteria
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.