Excel IF function with multiple conditions using the OR function
How to use the IF function if you need to pass multiple conditions over different cells.
Let’s say we have an online T-shirt shop and we have a spreadsheet with sales data. We want to a check whether the sales were of Male T-shirts of the color black. And we are going to present the result in a separate column called “Check”. How are we going to get the data we need?
The formula used here is =IF(OR(F2=”Black”;G2=”Male”);TRUE;””).
Now let’s explain this a bit. The IF function syntax is as follows:
=IF(logical_test; value_if_true; value_if_false).
So in this case if the logical_test returns true we will fill in the value TRUE. If the logical_test return false we will leave the cell empty. Of course you can change these values in whatever you desire.
Since we need multiple conditions for our logical test to pass we use the OR function to do this for us. The OR syntax is as follows:
=OR(logical1; [logical2]; …)
You can add up to 255 conditions to test, returning TRUE or FALSE. In fact if we remove the IF function from this example and only leave =OR(F2=”Black”;G2=”Male”) it will result in TRUE or FALSE. But since we want a bit more control over what we present in the cells in the H column we use the IF function.
In this example we use OR(F2=”Black”;G2=”Male”) to test two conditions for the cell H2. The first is to check if cell F2 has the text “Black”. The second is to check if cell G2 has the text “Male”. Only if the two conditions are met, the OR function will return TRUE. Else it will return FALSE.
Dragging the value in cell H2 down will automatically change the corresponding cells. For example for cell H6 the OR function will contain OR(F6=”Black;G6=”Male). If you do not want to change these values and only check on a fixed combination of cells, you can use the $ dollar sign to block this. Example: OR($F2=”Black;$G2=”Male”). In this case the check for cell H6 will also refer to the cells F2 and G2 and not to F6 and G6.
Nested OR function.
It is possible to nest OR functions. And the new example below I have some blank cells in the “Color” column. Let’s say we know that all empty cells should be “Black”. We can write a function that corrects this error.
Here we used =IF(OR(ISBLANK(F2);OR(F2=”Black”));”Black”;F2) where within the OR function that checks if, in this case, cell F2 is blank I have nested another OR function checking if cell F2 contains the text “Black”. In both cases it will return the text “Black” in any other case it will return the value of cell F2.
