The IF function is one of the most commonly used in Excel. The function can test a single condition as well as perform multiple complex logic tests. We can thus control the execution of Excel tasks using the IF function.
This is important since it enables us to perform actions depending on whether they meet certain conditions defined by the IF formula. We can also add data to cells using the function if other cells meet any of the conditions defined in the IF formula.
To add multiple conditions to an IF formula, we simply add nested IF functions.
In this article, you will learn the basics of the IF function and how to come up with an array of IF formulas. You will also learn the nested IF’s function.
Let’s get started!
1. Basic IF formula that tests a single condition
A single IF statement tests whether a condition is met and returns a value if TRUE and another value if FALSE.
IF (logical test, ‘value_if_true’, ‘value_if_false’)
In the example below, let us test whether the price is less than or equal to 2000. If the condition is true return cheap, otherwise return expensive!
Go to Cell C2 and type '=IF(B2<=2000,"CHEAP", "EXPENSIVE")'
The if formula above records or posts the String Good whenever the single condition B2<=2000.
2. IF formula with multiple conditions (Nested IF Functions.
If you have complex data or want to perform powerful data analysis, you might need to test multiple conditions at a time. You might need to perform a logical test across multiple conditions.
In such cases, you have to use an IF statement with multiple conditions/ranges in a single formula. These multiple IF conditions are normally called nested IF Functions. The function is used if want more than 3 different results.
The general syntax for IF function with multiple conditions is
=IF (condition one is true, do something, IF (condition two is true, do something, IF (conditions three is true, do something, else do something)))
Commas are used to separate conditions and IF functions in the IF formula. The ")" is used to close all IFs at the End of the formula while the " (" opens all IF functions. The number of "(" should be equal to that of ")".
In our above example, we can include multiple conditions in the IF formula as follows
=IF(B2<=2000,"CHEAP",IF(B2<=6000,"AFFORDABLE",IF(B2<=10000,"EXPENSIVE","LUXURIOUS")))
Press Enter to get the results and use the AutoFill feature to copy the formula to the rest of the cells.
3. IF formula with logical test
You can use an IF statement with AND and OR conditions to perform a logical test. IF your formula has AND function, the logical test returns TRUE if all the conditions are met; otherwise, it will return FALSE. While the OR Function returns TRUE if any of the conditions is met; otherwise it returns FALSE.
3.1 IF with AND Function
In the example below, a student is deemed to pass the exam if he/she has a score of 42 in Maths and 50 in English.
With this information, we can write the condition as follows;
AND (C14>=42, D14>=50)
To know whether the student passed or failed the exam. You can use the IF statement.
= IF(AND (C14>=42, D14>=50),”PASS”,”FAIL”)
Press enter and use the AutoFill feature to copy the formula to the rest of the cells.
3.2 IF with OR Function
The IF with OR Function works the same way as the AND function we have seen above. The only difference is that, if one of the conditions is TRUE then the result will be TRUE.
Follow the above process to apply the OR Function.
In cell E14, type = IF(OR (C14>=42, D14>=50),”PASS”,”FAIL”)
Press enter to get the results.
3. IF with ISNUMBER and ISTEXT Functions
You can use ISNUMBER and ISTEXT formula to find whether the cells have text value, number, or blank.
=IF(ISTEXT(A1), "Text", IF(ISNUMBER(A1), "Number", IF(ISBLANK(A1), "Blank", "")))
4. IF Formula with Multiple AND & OR Conditions
You can only apply AND & OR functions at a time if your task requires you to evaluate several sets of conditions. For example, if you are checking exam results using these criteria:
Condition 1: exam1>50 and exam2>50
Condition 2: exam1>40 and exam2>60
In this case, the exam is deemed passed if either of the conditions is passed.
Although the formula may seem tricky, you only need to express each condition as an AND statement and nest them in the OR function. That is because meeting all the conditions is not necessary, but either statement will serve the purpose. Therefore, the general formula will be:
OR(AND(B2>50, C2>50), AND(B2>40, C2>60)
You can now use the OR function to test the logic of IF and supply the desired value_if_true and value_if_false values. In the end, the final IF formula with multiple AND & OR statements will turn into:
=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")
5. Using the Array Formula
An Array formula can also help to get an Excel IF to test multiple conditions. For example, to evaluate the AND logical condition, you can use the asterisk:
IF(condition1) * (condition2) * …, value_if_true, value_if_false)
While to test conditions using OR logic, you will apply the plus sign:
IF(condition1) + (condition2) + …, value_if_true, value_if_false)
Finally, you can complete the Array formula by pressing Ctrl + Shift + Enter buttons together. The trick can also work as a regular formula in Excel 365 and Excel 2021 since it supports dynamic arrays.
Example: We can get “Pass” if cells B2 and C2 are greater than 50 by applying the formulas;
=IF((B2>50) * (C2>50), "Pass", "Fail") for AND logic
…, and
=IF((B2>50) + (C2>50), "Pass", "Fail") for OR logic.
6. IF With Other Excel Functions
Other Excel functions that can give the IF formula for multiple conditions include
6.1 IF#N/A Error in VLOOKUP Function
The VLOOKUP or any other lookup function returns a N/A error if it cannot find something. Therefore, you can return zero, blank, or text if #N/A to make your table neat. Here, you will need the following generic formula:
IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))
Example 1. If the lookup value in a cell (E1) is not found, the formula returns 0 as shown below;
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))
Example 2: If the lookup value is not found, the formula returns blank as shown below;
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))
Example 3: If the lookup value is not available, the formula returns a specific text as shown below;
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE))
6.2 IF With SUM, AVERAGE, MIN, and MAX Functions
Excel has the SUMIF and SUMIFS functions that you can use, to sum up values in cells using certain criteria. You can use the SUM function to test the IF logic of your business. For instance, you can apply this formula to determine the text labels based on the sum of cells B2 and C2 values;
=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))
From the formula, the return text label is “Good” if the sum of the two cells is greater than 130. If the sum is greater than 110, the return text is “Satisfactory”, while if the sum is 110 or below, the return text is “Poor”.
You can still embed the AVERAGE function similarly to test the IF logic and return a different text label based on the average score of the two cells. In this case, the generic formula will be;
=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))
If the sum of cells B2 and C2 is indicated in cell D2, you can use the MAX and MIN functions to identify the highest and lowest values through the formulas;
=IF(D2=MAX($D$2: $D$10), "Best result", "")
=IF(D2=MAX($D$2: $D$10), "Best result", "")
Lastly, you can nest the two functions into one another to have both text labels in one column to get the formula;
=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", ""))
6.3 IF with CONCATENATE Function
You can also use the CONCATENATE and IF functions to output the result of IF and some text into one cell. Here, you can use the formula;
=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))
From the formula, the student will get a "fantastic" result if the score in cell B1 is greater than 100. On the other hand, the result will be "well" if the score in cell B1 is greater than 50.
6.4 IF with ISERROR and ISNA Functions
These functions can only work in modern Excel versions where they trap and replace errors with another predefined value. For instance, the IFERROR function works in Excel 2007 and later, while the IFNA function works in Excel 2013 and later. When using earlier Excel versions, you will need the combinations of IF ISERROR and IF ISNA. So, what is the difference between these applications?
The answer is simple: IFERROR and ISERROR functions can handle all the possible errors resulting from Excel calculations, including VALUE, N/A, NAME, REF, NUM, DIV/0, and NULL errors. Contrary, IFNA, and ISNA handle only N/A errors.
Example: If you want to replace the #DIV/0 (divide by zero error) with a predefined text or value, you can use the formula;
=IF(ISERROR(A2/B2), "N/A", A2/B2), where;
A2 represents the value in cell A2
B2 represents the value in cell B2.
A2/B2 represents the results attained after dividing A2 by B2.
If the value in cell B2 is 0, the formula will return a N/A text.
I hope you found this tutorial useful.