If you’ve ever wanted to check whether data from your Google Sheets spreadsheet meets certain criteria, you can use AND and OR. These logical functions give you TRUE and FALSE responses, which you can use to sort through your data.
If you use AND with multiple arguments, all of those arguments must be true to get a TRUE response; otherwise, AND responds with FALSE. If you use OR, only one argument has to be true for OR to provide a TRUE response.
You can use AND and OR separately or within other functions, like IF.
Using the AND Function
You can use the AND function on its own or combined with other functions to provide a logical (TRUE or FALSE) test.
To get started, open a Google Sheets spreadsheet and click an empty cell. Type =AND(Argument A, Argument B) and replace each argument with the criteria you want to use. You can use as many arguments as you’d like, but you must have at least one for AND to work.
In the example below, we used three arguments. The first argument is the simple calculation of 1+1=2.
The second argues that the cell E3 equals the number 17.
Finally, the third argues that the value of cell F3 (which is 3) is equal to the calculation of 4-1.
As all three arguments are true, the AND formula responds with TRUE in cell A2. If we change any of these arguments, it will cause the AND formula in A2 to change the response from TRUE to FALSE.
In the example below, the AND formula in cell A3 has two correct arguments and one that’s incorrect (F3=10, while F3 actually equals 3). This causes AND to respond with FALSE.
Using the OR Function
While AND requires all the arguments it uses to be true, the OR function only requires one to be true for OR to respond with TRUE.
Like AND, you can use the OR function on its own or combine it with other functions. The same as with AND, you can use as many arguments as you want, but you must have at least one for it to work.
To use OR, click an empty cell and type =OR(Argument A, Argument B), and replace the arguments with your own.
In the example below, the formula using OR in cell A2 has one incorrect argument out of three (F3=10, where F3 actually equals 3).
Unlike when you use AND, one incorrect argument out of three will result in a TRUE result. For a FALSE result, all the arguments you use must be incorrect.
In the example below, the OR formulas in cells A4 and A5 returned a FALSE response because all three arguments in both formulas are incorrect.
Using AND and OR with IF
Because AND and OR are logical functions with TRUE and FALSE responses, you can also use them with IF. When you use IF, if an argument is TRUE, it returns one value; otherwise, it returns another value.
The format of a formula using IF is =IF(Argument, Value IF TRUE, Value IF FALSE). For example, as shown below, =IF(E2=1,3,4) causes IF to return the number 3 if cell E2 equals 1; otherwise, it returns the number 4.
Because IF only supports a single argument, you can use AND and OR to introduce complex logical tests with multiple arguments.
Using AND with IF
To use AND within an IF formula, type =IF(AND(AND Argument 1), Value IF TRUE, Value IF FALSE), and replace your AND argument (or arguments), and IF TRUE and IF FALSE values.
In our example below, we used IF with a nested AND formula in cell A2 with four arguments. All four arguments are correct, so the IF TRUE value (in this case, “Yes,”) is returned.
In cell A3, a similar IF with AND formula contains two incorrect arguments. As AND requires all arguments to be correct, IF returns the IF FALSE value, which is a different text value (“No”).
Using OR with IF
The same as with AND, you can also use OR with IF to create a complex logical test. Only one OR argument has to be correct for IF to return a TRUE response.
To use OR with IF, click an empty cell and type =IF(OR(OR Argument 1), Value IF TRUE, Value IF FALSE).
Replace the OR argument (or arguments), and your IF TRUE/FALSE values, as required.
In our examples below, the two IF with OR formulas in cells A2 and A3 returned the IF TRUE text value (“Yes”). All four arguments are correct in the A2 IF with OR formula, while A3 has two incorrect arguments out of four.
In cell A4, all four arguments in the IF with OR formula are incorrect. This is why the overall IF formula returns the IF FALSE text value (“No”), instead.
