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.

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

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

1. harman says:

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. James Carmichael says:

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!

1. harman says:

Thanks for this. Its working.

1. James Carmichael says:

Hi Harman…You are very welcome! Thank you for the update!