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
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.
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!
Thanks for this. Its working.
Hi Harman…You are very welcome! Thank you for the update!
Hello Mr. Zach Bobbitt,
I want some help regarding some formula. I want to insert item stock value to another sheet which have some item code. Plz. help how to find automatically same item code to another sheet and pick the value of the items.
Hi harman…To automatically find the same item code in another sheet and pick the value of the items in Excel, you can use the `VLOOKUP`, `INDEX` + `MATCH`, or `XLOOKUP` (in Excel 365 and later) functions. Here’s how to do it:
### **1. Using VLOOKUP**
– **Step 1**: Suppose you have two sheets, `Sheet1` (where you have the item codes and stock values) and `Sheet2` (where you want to insert the stock values).
– **Step 2**: In `Sheet2`, you have item codes in column A, and you want to fill in the stock value in column B.
– **Step 3**: Use the following formula in `Sheet2` cell B2:
“`excel
=VLOOKUP(A2, Sheet1!$A$2:$B$100, 2, FALSE)
“`
– `A2`: The cell in `Sheet2` where the item code is located.
– `Sheet1!$A$2:$B$100`: The range in `Sheet1` where the item codes (in column A) and stock values (in column B) are located.
– `2`: The column index from which to return the value (2nd column in this case).
– `FALSE`: Specifies that you want an exact match.
### **2. Using INDEX and MATCH**
– **Step 1**: Similarly, assume `Sheet1` has item codes in column A and stock values in column B. `Sheet2` has item codes in column A.
– **Step 2**: Use the following formula in `Sheet2` cell B2:
“`excel
=INDEX(Sheet1!$B$2:$B$100, MATCH(A2, Sheet1!$A$2:$A$100, 0))
“`
– `INDEX(Sheet1!$B$2:$B$100)`: This specifies the range where the stock values are located.
– `MATCH(A2, Sheet1!$A$2:$A$100, 0)`: This finds the position of the item code in `Sheet1` that matches the code in `Sheet2`.
### **3. Using XLOOKUP** (Excel 365 or later)
– **Step 1**: In `Sheet2` cell B2, use the following formula:
“`excel
=XLOOKUP(A2, Sheet1!$A$2:$A$100, Sheet1!$B$2:$B$100, “Not Found”)
“`
– `A2`: The item code in `Sheet2`.
– `Sheet1!$A$2:$A$100`: The range where the item codes are located in `Sheet1`.
– `Sheet1!$B$2:$B$100`: The range where the stock values are located in `Sheet1`.
– `”Not Found”`: This is the value to return if no match is found.
### **4. Applying the Formula to the Whole Column**
– Once you’ve entered the formula in cell B2, you can drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to other cells in column B.
This will automatically match the item codes and insert the corresponding stock values from `Sheet1` into `Sheet2`.
If you need further customization or face any issues, feel free to ask!
Hello Mr. Zach Bobbitt,
I want some help regarding Vlookup function. We used the Vlookup function to find automatically same item code to another sheet and pick the value of the items but sometimes the value exists but they give error. Can you help me regarding this problem
Hi Harman…When using the VLOOKUP function in Excel, getting errors even when the value exists can be frustrating. Here are some common issues and solutions that might help resolve this problem:
### 1. **Exact vs. Approximate Match:**
– **Problem:** By default, VLOOKUP looks for an approximate match if the `range_lookup` argument is omitted or set to `TRUE`. This can cause errors if the exact match isn’t found.
– **Solution:** Ensure you are using `FALSE` for an exact match. The formula should look like this:
“`excel
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
“`
### 2. **Leading/Trailing Spaces:**
– **Problem:** Extra spaces in either the lookup value or the table array can cause VLOOKUP to fail.
– **Solution:** Use the `TRIM()` function to remove any extra spaces in your data.
“`excel
=VLOOKUP(TRIM(A2), Sheet2!A:B, 2, FALSE)
“`
### 3. **Data Type Mismatch:**
– **Problem:** If the lookup value and the values in the table array are of different data types (e.g., one is text, and the other is a number), VLOOKUP may not find a match.
– **Solution:** Ensure both the lookup value and the values in the table array are of the same data type. You can use the `TEXT()` or `VALUE()` functions to convert them as needed.
“`excel
=VLOOKUP(TEXT(A2, “0”), Sheet2!A:B, 2, FALSE)
“`
### 4. **Hidden Characters or Non-Printable Characters:**
– **Problem:** Non-printable characters like line breaks or hidden characters might cause mismatches.
– **Solution:** Use the `CLEAN()` function to remove non-printable characters.
“`excel
=VLOOKUP(CLEAN(A2), Sheet2!A:B, 2, FALSE)
“`
### 5. **Range Not Sorted (When Using Approximate Match):**
– **Problem:** If you are using an approximate match (`range_lookup` set to `TRUE`), the first column of the table array must be sorted in ascending order.
– **Solution:** Sort the first column of your table array in ascending order, or switch to an exact match by setting `range_lookup` to `FALSE`.
### 6. **Column Number Issue:**
– **Problem:** The column index number in VLOOKUP might be pointing to a column outside of your table array’s range.
– **Solution:** Ensure the column index number is within the range of your table array.
### 7. **Merged Cells:**
– **Problem:** Merged cells in the lookup column or table array can cause VLOOKUP to behave unpredictably.
– **Solution:** Avoid using merged cells in your lookup column or table array.
### 8. **Missing Data or Incorrect References:**
– **Problem:** If the lookup value doesn’t exist or the table array is incorrectly referenced, VLOOKUP will return an error.
– **Solution:** Double-check your references and ensure that the lookup value exists in the first column of your table array.
### 9. **VLOOKUP in Large Datasets:**
– **Problem:** In large datasets, VLOOKUP may sometimes fail to return a match due to performance issues or data inconsistencies.
– **Solution:** Consider using INDEX and MATCH as an alternative to VLOOKUP, which can be more reliable in some cases:
“`excel
=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))
“`
By addressing these common issues, you should be able to resolve most VLOOKUP errors. If the problem persists, double-check the specific cells where the error occurs to see if there are any inconsistencies.
Hello Mr. Zach Bobbitt,
We are facing same issue in some items like mention as above after using your functions. when I used function again only on error value they give right value. I have no much time to check all error value. Plz. help and give a appropriate solution to solve this problem and thanks for the help.
Hi Harman…Please provide the exact verbiage of the errors you are encountering so that we may better assist you.
Hello Mr. Zach Bobbitt,
When I applied VLLOKUP function (IFERROR(VLOOKUP(W19,STOCK!A17:F1080,5,FALSE),”DATA NOT FOUND”)) to the entire sheet we received this error ex: Item(36X41 MM RS) Stock(DATA NOT FOUND) order(109). When i checked the error and reapplied this function only on the error value we got the correct value. ex: Item(36X41 MM RS) Stock(1320) order(109). This problem are occurred on multiple value.
Hi Harman…The issue you’re experiencing with the `VLOOKUP` function could be due to several factors, such as incorrect data types, leading or trailing spaces, or timing issues with data updates in Excel. Here’s a step-by-step guide to troubleshoot and resolve the problem:
### 1. **Check for Leading or Trailing Spaces:**
– Sometimes, cells may contain extra spaces that cause `VLOOKUP` to fail. You can use the `TRIM` function to remove any leading or trailing spaces in the data.
– Modify your formula as follows to include `TRIM`:
“`excel
IFERROR(VLOOKUP(TRIM(W19), STOCK!A17:F1080, 5, FALSE), “DATA NOT FOUND”)
“`
### 2. **Ensure Consistent Data Types:**
– Make sure that the data in `W19` and the `STOCK` sheet column `A` have the same data type (e.g., both are text or both are numbers). If there’s a mismatch, Excel may not find the match.
– If necessary, you can use the `TEXT` or `VALUE` functions to ensure consistent data types.
### 3. **Check for Hidden Characters:**
– Sometimes, hidden characters (like non-breaking spaces) can cause issues. You can use `CLEAN` in combination with `TRIM` to remove such characters:
“`excel
IFERROR(VLOOKUP(TRIM(CLEAN(W19)), STOCK!A17:F1080, 5, FALSE), “DATA NOT FOUND”)
“`
### 4. **Use Exact Match (FALSE) in VLOOKUP:**
– Since you’re already using `FALSE` for the exact match, make sure the data in both columns is perfectly aligned. Even a small discrepancy can cause `VLOOKUP` to fail.
### 5. **Array Formula for Bulk Application:**
– If the issue persists when applying the formula across a large range, try using an array formula (for Excel versions that support it):
“`excel
=IFERROR(INDEX(STOCK!E17:E1080, MATCH(TRIM(W19), STOCK!A17:A1080, 0)), “DATA NOT FOUND”)
“`
– Press `Ctrl+Shift+Enter` after typing the formula to apply it as an array formula.
### 6. **Refresh or Recalculate:**
– Sometimes, Excel might not update all the cells properly due to its recalculation settings. You can force Excel to recalculate by pressing `Ctrl+Alt+F9`.
### 7. **Check for Data Update Timing:**
– If the `STOCK` sheet data is being updated dynamically or via an external link, there might be timing issues causing `VLOOKUP` to fail temporarily. Ensure that all data is updated before running the lookup.
### 8. **Manual Re-Application:**
– As you mentioned, manually reapplying the formula resolved the issue. This suggests that Excel might have had a temporary glitch. If this happens frequently, consider breaking your task into smaller batches or applying the formula to a few cells at a time.
By addressing these potential issues, you should be able to reduce or eliminate the `VLOOKUP` errors and get consistent results across your entire sheet.
Hello Mr. Zach Bobbitt,
Thanks for your help, but all these functions are not useful for me. Have any other way to solve this problem because i am facing same issue.
Hi Harman…Please clarify the issue so that we may better assist you.
Hello Mr. Zach Bobbitt,
Can i send you our sheet.
Hi Harman…We are happy to answer any questions, however we cannot receive spreadsheets.
Hello Mr. James Carmichael
We have facing some issue in excel. Plz. support me to solve this problem. When we apply the function =IFERROR(VLOOKUP(B4,STOCK!A2:F1065,5,FALSE),”DATA NOT FOUND”) to a sheet and drag it down, the function doesn’t search the entire sheet, instead, it starts searching from the next cell =IFERROR(VLOOKUP(B12,STOCK!A12:F1075,5,FALSE),”DATA NOT FOUND”). How can we solve this issue, as the items on both sheets are not in sequence, causing incorrect data retrieval
Hi Harman,
The issue you’re facing occurs because the range reference in the `VLOOKUP` formula (`STOCK!A2:F1065`) is not locked. When you drag the formula down, Excel shifts the row references (e.g., from `A2:F1065` to `A12:F1075`) because they are relative references.
To solve this problem, you need to lock the lookup range using absolute references. Here’s how:
### Corrected Formula:
“`excel
=IFERROR(VLOOKUP(B4,STOCK!$A$2:$F$1065,5,FALSE), “DATA NOT FOUND”)
“`
### Explanation:
– **`$A$2:$F$1065`**: The `$` symbols lock the row and column references, ensuring that the range does not change when the formula is copied or dragged.
– The rest of the formula remains the same.
### Steps to Fix:
1. Edit your formula to include the `$` symbols for the range reference (`$A$2:$F$1065`).
2. Drag the formula down the column.
This will ensure that the `VLOOKUP` function always searches within the entire specified range, regardless of where the formula is copied.
Let me know if you need further assistance!
Best regards,
James Carmichael
Thanks Mr. James Carmichael for help its working.
You are very welcome harman! We greatly appreciate your feedback and support! Keep us posted on your progress and let us know if we can help with content from our other posts.
Hello Mr. James Carmichael
We have facing some issue another issue in excel. Plz. help to solve this.
I applied the function =IFERROR(VLOOKUP(B8,STOCK!$A$2:$F$1065,5,FALSE),”0″) and then checked the whole sheet, which displayed 0 because the data was not found. Now, I want to apply conditional formatting to this. If the data is not found and 0 is displayed, I want the row color to turn red. How can I do this?
Hello Mr. James Carmichael
We have facing some another issue in excel. Plz. help to solve this.
When i applied the function =IFERROR(VLOOKUP(B8,STOCK!$A$2:$F$1065,5,FALSE),”0″) and then checked the whole sheet, which displayed 0 because the data was not found. Now, I want to apply conditional formatting to this. When I manually type ‘0’, the row color turns red, but when I use the VLOOKUP function and it returns ‘0’ because the data is not found, the row color does not turn red. I want the row color to turn red. How can I do this?
Hi harman…The issue arises because the `VLOOKUP` function’s result (`”0″`) and a manually typed value (`0`) are treated differently in Excel’s conditional formatting. This happens because `IFERROR(VLOOKUP(…), “0”)` outputs the value `”0″` (a text string) rather than the numeric value `0`.
To fix this and apply conditional formatting correctly, you can take the following steps:
—
### Steps to Apply Conditional Formatting:
1. **Identify the Issue:**
Ensure the `IFERROR` formula outputs a consistent format. If you want the result to be a number (`0`), modify your formula slightly:
“`excel
=IFERROR(VLOOKUP(B8, STOCK!$A$2:$F$1065, 5, FALSE), 0)
“`
This will return the numeric value `0` instead of the text `”0″`.
2. **Apply Conditional Formatting:**
– Select the rows or cells you want to format.
– Go to **Home > Conditional Formatting > New Rule**.
– Select **Use a formula to determine which cells to format**.
– Enter the following formula (assuming your VLOOKUP results are in column `C` starting from row 8):
“`excel
=C8=0
“`
– Click **Format** and choose the formatting style you want (e.g., red fill).
– Click **OK** to save the rule.
3. **Verify the Result:**
The conditional formatting will now correctly turn the row red whenever the `VLOOKUP` function returns `0`.
—
### If You Must Keep `”0″` as a Text String:
If you cannot modify the `IFERROR` formula to return a numeric value (due to other dependencies), adjust the conditional formatting formula to account for the text `”0″`:
1. Use this conditional formatting formula:
“`excel
=C8=”0″
“`
This explicitly checks for the text `”0″`.
2. Apply the formatting style as described above.
—
Both approaches will ensure the row color turns red based on the condition set. Choose the one that best fits your needs!
Mr. James Carmichael thank you very much for your help.
You are very welcome harman! Let us know if we can help answer any questions going forward regarding our content.
Hi James,
I have the following data set and have used the formula below in column I to return a True or False value:
=IF(OR($C4:$H4=”N”,$C4:$H4=”N/A”),TRUE,FALSE)
A B C D E F G H I
Bay 1 00:00 Y N/A N/A N/A Y N/A TRUE
Bay 2 00:01 N N N N N N FALSE
Bay 3 00:02 Y Y Y Y Y Y TRUE
Bay 4 00:03 Y Y N Y Y Y TRUE
So if column B to H are EITHER Y or N/A the result should be TRUE, however if any of the columns (B to H) are N the result should be FALSE – therefore the result for Bay 4 is currently incorrect. Can you please advise what the correct formula should be? Many thanks.
Hi Colin…To correct your formula, you need to adjust it to account for your stated condition: the result should be **TRUE** only if all the cells in columns `C:H` are either `Y` or `N/A`. If **any** cell in columns `C:H` is `N`, the result should be **FALSE**.
The formula you provided currently checks if **any** cell is either `N` or `N/A`, which doesn’t match your desired logic.
Here’s the corrected formula:
“`excel
=IF(COUNTIF($C4:$H4, “N”) > 0, FALSE, TRUE)
“`
### Explanation:
1. `COUNTIF($C4:$H4, “N”) > 0`: This checks if there is any occurrence of `N` in the range `C4:H4`.
– If there is at least one `N`, the result will be **FALSE**.
– If there are no `N`s, the result will be **TRUE**.
### Another approach using `AND`:
If you’d like to use `AND`, this formula achieves the same result:
“`excel
=AND(COUNTIF($C4:$H4, “N”)=0, COUNTIF($C4:$H4, “Y”)+COUNTIF($C4:$H4, “N/A”)=COLUMNS($C4:$H4))
“`
This ensures:
1. There are no `N`s in the range.
2. All the remaining values are either `Y` or `N/A`.
### Updated Output:
| Bay | Time | C | D | E | F | G | H | Result |
|——-|——-|—–|——|——|——|—–|——|——–|
| Bay 1 | 00:00 | Y | N/A | N/A | N/A | Y | N/A | TRUE |
| Bay 2 | 00:01 | N | N | N | N | N | N | FALSE |
| Bay 3 | 00:02 | Y | Y | Y | Y | Y | Y | TRUE |
| Bay 4 | 00:03 | Y | Y | N | Y | Y | Y | FALSE |