Contents
For large Excel data, and require you to filter out values that satisfy many different conditions, you will need to combine the IF function with some other basic Excel functions to be applicable simultaneously. many conditions. The following article by Thuthuat.tip.edu.vn I will introduce you very detailed and specific multi-condition IF functionLet’s follow along!
IF function in Excel
Before learning about the multi-condition IF function, let’s find out what the IF function is. The IF function is one of the most popular functions in Excel, it allows you to perform logical comparisons between a value and the desired value.
So an IF statement can have two results. The first result is if your comparison is True, the second result is if your comparison is False.
Conditional IF function
After knowing what the IF function is, we continue to learn about the conditional IF function. The syntax for the conditional IF function is as follows: IF(logical_test, [value_IF_true], [value_IF_false])
As you can see, the IF function has 3 parameters, but only the first parameter is required, and the other 2 are optional.
logical_test: Is a value or logical expression that has the value TRUE (true) or FALSE (false). Required. For this parameter, you can specify whether it is a character, a date, a number, or any comparison expression.
For example, Your logical expression could be either B1=”sold”, B1<12/1/2014, B1=10 or B1>10.
Value_IF_true: Is the value that the function will return if the logical expression gives the value TRUE or in other words the condition is satisfied. Not required.
For example, the following formula will return the word “Good” if the value in cell B1 is greater than 10: =IF(B1>10, “Good”)
Value_IF_false: is the value that the function will return if the logical expression gives the value FALSE or in other words, the condition is not satisfied. Not required.
Example: If you add the third variable “Bad” to the formula in the example above, it will return the word “Good” if the value in cell B1 is greater than 10, otherwise the return value will is “Bad”: =IF(B1>10, “Good”, “Bad”)
Multi-condition IF function – Nested IF function
If the IF function normally has only one condition, then the multi-condition IF function will have many different conditions, maybe 2 conditions, 3 conditions or more.
The multi-condition IF function is also known as nested IF.
first. IF function 2 conditions
The 2-condition IF function is often used when the problem has 3 arguments to compare.
General formula:
=IF(logical_test_1, [value_IF_true]IF((logical_test_2, [value_IF_true], [value_IF_false]))
For example: Calculating the amount after the train ticket discount knows that children aged 10 and under will receive half the ticket discount and free tickets for the elderly over 70 years old.
The formula would be: =IF(B2>70;C2-C2;IF(B2>10;C2;C2/2))
2. The IF function has 3 conditions
We use the IF function with 3 conditions when there are 4 criteria to compare.
General formula:
=IF(logical_test_1, [value_IF_true]IF((logical_test_2, [value_IF_true]IF(logical_test_3, [value_IF_true], [value_IF_false])))
For example, based on grades, rank students’ academic performance according to the following criteria:
Score > 8: Excellent student
From 6.5 to 7.9: Good student
From 3.5 to 6.4: Average student
Less than 3.5: Weak Student
To have the most concise formula, users need to use the IF function with many conditions, specifically here the IF function with 3 conditions. Note, when sorting, should arrange the most important conditions on top.
= IF(C2>=8;”Excellent”;IF(C2>=6.5;”Excellent”; IF(C2>=3.5;”Average”;”Weak”)))
However, if the comparison condition is too much, up to 5 or 6 conditions, users should think about using another function or using a combination of multiple functions to make the formula shorter and easier to understand.
Multi-condition IF function – Combine with other functions
IF function with date condition
When using the IF function to compare dates, it will not be recognized as a string.
For example: Check the list of people who receive incentives, know that the offer applies to people born after January 1, 2000.
=IF(B2>DATEVALUE(“01/01/2000″);”Accept”;”Not receive”)
The IF function combines Vlookup or Hlookup
first. The IF function combines Vlookup
The IF function that combines Vlookup in Excel returns True / False, Yes / No, … .
The most common use case of the IF function in combination with Vlookup is to compare the values returned by the Vlookup function with the sample value and return the result as Yes / No or True / False.
The syntax of the IF function in combination with Vlookup in Excel has the following form:
=IF(Vlookup(Lookup_value,Table_ array,Col_index_Num,[Range_lookup]))
In there:
– Lookup value: This value refers to the cell or text value we are looking for.
– Table_array: defines the range of cells or data areas that we want to search for the value.
– Col_index_number: the number of columns that we want to return the value.
– Range_lookup: this parameter is True or False, get exact match or similar data.
Suppose for a data table there is a list of items listed in column A and the quantity of these items is in column B, and you need to check the quantity of an item in cell E1 to notify the user. that the item is in good condition or has been sold out.
The Vlookup function formula has the following form:
=VLOOKUP(E1,$A$2:$B$10.2,FALSE)
Next, use the IF command to compare the results returned by the Vlookup function with 0 and return the result “No” if this value is equal to 9 or “Yes” if it is not:
=IF(VLOOKUP(E1,$A$2:$B$10.2,FALSE)=0,”No”,”Yes”)
Instead of using the result as Yes/ No, we can return the result as TRUE / FALSE or Stock / Sold (in stock / out of stock) by using the following Excel function:
=IF(VLOOKUP(E1,$A$2:$B$10,2)=0,”Sold out”,”In stock”)
2. IF function combines Hlookup
Similar to Vlookup
The syntax of the IF function in combination with Hlookup in Excel has the following form:
=IF(Hlookup(Lookup_value,Table_ array,Row_index_Num,[Range_lookup]))
Multi-condition IF function combined with AND . function
AND function: If the test condition contains the AND function, Microsoft Excel will return TRUE if all conditions are met; otherwise FALSE(False) will be returned.
Suppose, you have a table with the results of two test scores. The first score, stored in column A, must be equal to or greater than 20. The second score, listed in column B, must equal or exceed 30. Only when both of the above conditions are met, the student will pass the exam.
The easiest way to create a proper formula is to write down the condition first, and then incorporate it into your IF function test argument:
Condition: AND(B2>=20; C2>=30)
IF/AND Formula: =IF((AND(B2>=20;C2>=30));”Pass”;”Fail”)
Using this multi-condition IF function, you will ask Excel to return “Pass” if the value in column C >= 20 and the value in column D >= 30. Otherwise, the multi-condition IF function formula will returns “Sliding”, like the example in the image below.
Multi-condition IF function combined with OR . function
OR function: In case of using the OR function in the test, Excel will return TRUE if any of the conditions are met; otherwise will return FALSE(False).
You use a combination of the IF and OR functions in the same way as with the AND function above. The difference from the IF formula in Excel and AND above is that Excel will return TRUE if at least one of the specified conditions is met.
So the formula above would be modified in the following way:
=IF((OR(B2>=20; C2>=30)); “Pass”; “Fail”)
Column D will return “Pass” if the first score is equal to or greater than 20, or the second is equal to or greater than 30.
Multi-condition IF function combines AND and OR
In case you have to evaluate your data based on multiple conditions, besides using the IF function, you will have to use both AND and OR functions at the same time.
In the table above, let’s say you have the following criteria to evaluate your student’s passing:
Condition 1: column B >= 20 and column C >= 25
Condition 2: column B >= 15 and column C >= 20
If one of the above conditions is met then you are considered to have passed, otherwise failed.
The recipe looks complicated, but it’s actually not that difficult. You just need to represent the two conditions as AND statements and put them in the OR function because it doesn’t require both conditions to be met, just one of them being met is enough: OR(AND(B2>=20;C2>=25);AND(B2>=15;C2>=20)
Finally, use the OR function above as a logical test condition in the IF function and provide the TRUE and FALSE arguments. As a result you will get the following Excel IF formula with multiple AND/OR conditions:
=IF(OR(AND(B2>=20;C2>=25);AND(B2>=15;C2>=20)); “Pass”; “Fail”
Of course, you’re not limited to just using AND/OR in Excel IF formulas, but can use as many logic functions as your business logic requires, as long as:
In Excel 2016, 2013, 2010, and 2007, the formula cannot contain more than 255 arguments, and the total length of the formula cannot exceed 8,192 characters.
In Excel 2003 and below, up to 30 arguments can be used, and the total formula length cannot exceed 1,024 characters.
Multi-condition IF function exercises with solutions
Download the exercise file of the IF function with multiple conditions:
>> Download link below:
The answer
2.1. Calculate the monthly salary column.
To calculate this part is very simple: we just need to take [lương ngày] * [số ngày công] will calculate the monthly salary.
We enter the formula in cell F3 with the following formula: = D3 * E3
With the formula as the cell address as above, we are ordering excel to calculate the employee’s salary in line 3 (Cao Van Co) with a daily salary of 50,000/day and the number of working days in a month is 26 days.
Then copy the formula in cell F3 down for the remaining cells to complete the monthly salary column.
Because the formula is calculated by the relative address of 2 cells in 2 columns of workday and daily salary, so when copying the formula down the lines below, the cell address also changes and the value used to calculate salary also changes. change accordingly.
2.2. Bonus calculation – Combine multiple IF functions with multiple conditions (nested IFs) here.
The bonus conditions are as follows:
If the number of working days >=25: Bonus = 20% * monthly salary
If the number of working days >=22: Bonus = 10% * monthly salary
If the number of working days < 22: Bonus = 0
The IF formula is used to calculate the bonus in this case:
Bonus = IF (workday >=25, 20% * monthly salary, IF (workday >=22, 10% * monthly salary, 0))
Please see the detailed recipe in the picture below:
1. Cao Van Co: the number of working days is 26 > 25 => the bonus will be 20% of the salary – satisfying the first condition
2. Bui Thi Fat: the number of working days is 23 < 25 but > 22 => the bonus is only 10% of salary – not satisfying condition 1, but satisfying condition 2
3. Truong Van Sinh: the number of working days is 20 < 22, so of course it is less than 25, so it does not satisfy both the first two conditions of the IF function => the bonus is 0.
And above are Thuthuat.tip.edu.vn’s shares on how to apply the IF function with multiple conditions. If you have any questions, you can leave a comment below to be answered. In addition, you can refer to some other articles below:
Hope the answers about the multi-condition IF function above will be useful to you. Don’t forget to Like, Share and visit Thuthuat.tip.edu.vn regularly to get more new knowledge exclusively for office workers.
Microsoft Office –