In Excel’s IF statement formula, you face a situation when you need to apply more than one logical test and want to return a value when all the applied logical tests or conditions are TRUE. This is the situation where IF and AND functions come into action as a combined IF AND statement.
The Excel AND Function Combined with IF
The Excel AND function is one of the logical functions, and it returns TRUE if all conditions are met. Otherwise, it returns FALSE if any of the applied conditions do not meet. The syntax of AND function is;
AND(logical1, [logical2], …..)
In the IF statement, the AND function is used in logical_test argument part to test multiple logical conditions, and it returns a value if all conditions are TRUE, else it returns something else. So as a combined IF AND statement the syntax would be;
IF(AND(logical1, [logical2], ...), [value_if_true], [value_if_false])
You can see the AND function has replaced logical_test argument of the IF statement because the AND function is itself a logical function. You need to apply all logical tests in the AND function of the IF statement those need to be TRUE to return a value in value_if_true argument, else return another value in value_if_false argument, if any of the applied logical tests are FALSE.
Example of IF AND Function
Suppose you have a data set of men’s shirts in various sizes and colors. Each product has different prices for each size and color available in stock. You need to check the availability of a certain size and color combination in a long list of product’s sizes and colors combinations.
Using the IF AND statement you need to check the availability of all those products that meet both conditions, like “Available,” otherwise leave the status as a blank value.
You need to supply both logical conditions in the AND function. The AND function will return TRUE if any product meets both conditions, and IF statement will return value “Available” provided in value_if_true argument, otherwise the IF statement will return a blank value (“”), if any of the conditions supplied in the AND function are FALSE, such as;
=IF(AND(B2="M",C2="Red"),"Available","")
You can also supply logical tests values as hard-coded values as given above or you can use cell references in the AND function containing logical conditions, such as
=IF(AND(B5=$B$2,C5=$C$2),"Available","")
You need to make the cell references absolute that contain logical tests’ values by using $ sign with column and row references so that these cell references do not change when you copy the formula to other rows containing data as shown above.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment