Excel IF function is one of my favorite function in Excel. It is very useful and very simple to use. It does wonders when you want to create some complicated excel Formulas. You can extend the functionalities of IF function by nesting it or using along with other Excel Functions such as VLOOKUP, AND, OR etc.
How Excel IF Function Works?
Excel IF Function checks for a condition(Criteria) and returns a value if the condition is met or returns another value if the criteria is not met. The criteria can be anything such as comparison, text check or error check etc. You can use nested IF functions upto 98 times in a excel worksheet.
Syntax of Excel IF Function
=if(condition, value if the condition is met, value if condition is not met)
=if(A1>10, “Greater Than 10”, “Less than 10”)
The Above IF formula returns Greater than 10 if the cell A1 has the value greater than 10 else returns Less than 10.
Using IF function with AND
As said earlier, you can use AND function inside IF while you want a value to be written provides multi conditions were met. AND function returns TRUE if and only if all the conditions are TRUE.For example, you have a value in cell A1. You want to check whether the number is in between 10 to 15. You can use the IF function as mentioned below.
Using IF function with OR
IF and OR is used when you want to check any of the conditions is met. The OR Function returns true if any of the condition is met. For example you have want to check out of range if the number in cell A1 is less than 0 or greater than 100. You can use IF function with OR to calculate this as mentioned below.
Using Nested IF Function
Using IF functions inside another IF function is called as nested IF functions. It is useful when you want to dig into the more detailed checks. For example you want to check if the number you have entered is a negative or positive in the cell A1. First we need to check whether the entered value is number. If it is a number, then we need to check whether it 0. Then we need to check whether it is greater than 0 or less than 0 to find out the value entered is positive or negative. The nested IF function in Excel looks like as mentioned below.
=IF(ISNUMBER(A1), IF(A1=0,”NIL”,IF(A1>0,”Positive Number”,”Negative Number”)),”It is not a Number”)
The above uses ISNUMBER function to check for the whether the entered value is number or not.
To learn how to use IF function with vlookup read my post on how to do vlookup on different ranges based on criteria.