Excel: How to Use an IF Function with 3 Conditions


You can use the following formulas to create an IF function with 3 conditions in Excel:

Method 1: Nested IF Function

=IF(C2<15, "Bad", IF(C2<20, "OK",  IF(C2<25, "Good", "Great")))

Method 2: IF Function with AND Logic

=IF(AND(A2="Mavs", B2="Guard", C2>25), "Yes", "No")

Method 3: IF Function with OR Logic

=IF(OR(A2="Mavs", B2="Guard", C2>25), "Yes", "No")

The following examples show how to use each formula in practice with the following dataset in Excel:

Example 1: Nested IF Function

We can type the following formula into cell D2 to return a specific value based on the value for each player in the Points column:

=IF(C2<15, "Bad", IF(C2<20, "OK",  IF(C2<25, "Good", "Great")))

We can then drag and fill this formula down to each remaining cell in column D:

Here’s what this formula did:

  • If the value in the Points column is less than 15, return Bad.
  • Else, if the value in the Points column is less than 20, return OK.
  • Else, if the value in the Points column is less than 25, return Good.
  • Else, return Great.

Example 2: IF Function with AND Logic

We can type the following formula into cell D2 to return “Yes” if three conditions are met for a specific player or “No” if at least one of the conditions is not met:

=IF(AND(A2="Mavs", B2="Guard", C2>25), "Yes", "No")

We can then drag and fill this formula down to each remaining cell in column D:

Here’s what this formula did:

  • If the value in the Team column was “Mavs” and the value in the Position column was “Guard” and the value in the Points column was greater than 25, return Yes.
  • Else, if at least one condition is not met then return No.

Example 3: IF Function with OR Logic

We can type the following formula into cell D2 to return “Yes” if one of three conditions are met for a specific player or “No” if none of the conditions are met:

=IF(OR(A2="Mavs", B2="Guard", C2>25), "Yes", "No")

We can then drag and fill this formula down to each remaining cell in column D:

Here’s what this formula did:

  • If the value in the Team column was “Mavs” or the value in the Position column was “Guard” or the value in the Points column was greater than 25, return Yes.
  • Else, if none of the conditions are met then return No.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use COUNTIF with Multiple Ranges
Excel: A Simple Formula for “If Not Empty”
Excel: How to Use a RANK IF Formula

4 Replies to “Excel: How to Use an IF Function with 3 Conditions”

  1. Hello Mr. Zach Bobbitt,
    I want some help regarding algorithm. I want to insert below conditions in function with three different conditions and with highlighting row in one go.
    Ex: if H2>0 THAN INPUT COMES “OK” ELSE “H2” ANF IF(G2>F2) THAN ROW COLOR RED AUTOMATICALLY.

    1. Hi harman…To achieve the functionality you’re looking for in Excel, you need to use a combination of the `IF` function and Conditional Formatting. Here’s a step-by-step guide:

      ### Step 1: Using the `IF` Function for Multiple Conditions

      You can use nested `IF` functions to handle multiple conditions in a single formula. Based on your example, you want to insert the following conditions:

      1. If `H2 > 0`, then return “OK”.
      2. If `H2 <= 0`, then return "H2". 3. If `G2 > F2`, then change the row color to red.

      For the first two conditions, you can use the following formula in the cell where you want the result:

      “`excel
      =IF(H2 > 0, “OK”, “H2”)
      “`

      ### Step 2: Conditional Formatting to Highlight the Row

      To automatically color the row red when `G2 > F2`, you need to use Conditional Formatting:

      1. **Select the range** you want to apply the formatting to. If you want to format entire rows based on the condition, you can select the entire rows or a specific range of cells in those rows.
      2. **Go to the Home tab** on the Ribbon.
      3. **Click on Conditional Formatting** in the Styles group.
      4. **Select New Rule**.
      5. Choose **Use a formula to determine which cells to format**.
      6. Enter the following formula in the “Format values where this formula is true” box:

      “`excel
      =$G2 > $F2
      “`

      Note: Ensure the dollar signs (`$`) are correctly placed to apply the condition to the entire row based on the value in column G and F.

      7. Click on the **Format** button.
      8. Go to the **Fill tab** and select the color red.
      9. Click **OK** to apply the formatting rule.

      ### Putting It All Together

      1. **Enter the `IF` function** in the cell where you want the result (e.g., I2):

      “`excel
      =IF(H2 > 0, “OK”, “H2”)
      “`

      2. **Apply Conditional Formatting** to highlight rows where `G2 > F2`:

      – Select the range you want to format (e.g., A2:Z100 if you want to format rows 2 through 100).
      – Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
      – Enter the formula `=$G2 > $F2`.
      – Set the format to fill the row with red color.

      ### Example

      Let’s assume you are working with rows 2 to 10 and columns A to Z. Follow these steps:

      1. **Enter the IF function in cell I2** (or wherever you need the output):

      “`excel
      =IF(H2 > 0, “OK”, “H2”)
      “`

      2. **Apply Conditional Formatting**:

      – Select the range `A2:Z10`.
      – Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
      – Enter `=$G2 > $F2`.
      – Choose a red fill color.

      This setup will ensure that:
      – Cell I2 (or your specified cell) will display “OK” if `H2 > 0`, otherwise “H2”.
      – The entire row will turn red if `G2 > F2`.

      If you have any further questions or need additional assistance, feel free to ask!

Leave a Reply

Your email address will not be published. Required fields are marked *