If you want to run a logical test in a Google Sheets formula, providing different results whether the test is TRUE or FALSE, you’ll need to use the IF function. Here’s how to use it in Google Sheets.
As the name suggests, IF is used to test whether a single cell or range of cells meets certain criteria in a logical test, where the result is always either TRUE or FALSE.
If the IF test is TRUE, then Google Sheets will return a number or text string, perform a calculation, or run through another formula.
If the result is FALSE, it’ll do something completely different. You can combine IF with other logical functions like AND and OR or with other nested IF statements.
Using the IF Function
The IF function can be used on its own in a single logical test, or you can nest multiple IF statements into a single formula for more complex tests.
To start, open your Google Sheets spreadsheet and then type =IF(test, value_if_true, value_if_false) into a cell.
Replace “test” with your logical test and then replace the “value_if_true” and “value_if_false” arguments with the operation or result that Google Sheets will provide when the result is either TRUE or FALSE.
In the example shown below, an IF statement is used to test the value of cell B3. If cell B3 contains the letter B, then the TRUE value will be returned in cell A3. In this case, that’s a text string containing the letter A.
If cell B3 doesn’t contain the letter B, then cell A3 will return the FALSE value, which, in this example, is a text string containing the letter C.
In the example shown, cell B3 contains the letter B. The result is TRUE, so the TRUE result (the letter A) is returned in A3.
Calculations also work well as a logical test. In the following example, the IF formula in cell A4 is testing whether cell B4 has a numerical value equal to, or greater than, the number 10. If the result is TRUE, it returns the number 1. If it’s false, it returns the number 2.
In the example, cell B4 has a value of 9. This means the result of the logical test is FALSE, with the number 2 shown.
Nested IF Statements
If you’d like to perform a longer, complex logical test, you can nest multiple IF statements into the same formula.
To nest multiple IF statements together into a single formula, simple type =IF(first_test, value_if_true, IF(second_test, value_if_true, value_if_false)). While this only shows a single nested IF statement, you can nest as many IF statements together as you require.
As an example, if cell B3 equals 4, then the IF formula in A3 returns a 3. If cell B3 doesn’t equal 4, then a second IF statement is used to test if cell B3 has a value less than 10.
If it does, return the number 10. Otherwise, return a 0. This example test has its own nested IF statement as the first “value_if_false” argument, requiring the first test to be FALSE before the second test is considered.
The example above shows all three potential results of this test. With the first logical test (B3 equals 3) returning a TRUE result, the IF formula in cell A3 returned the number 4.
The second logical test returned another TRUE result in cell A4, with the value of B4 less than 10.
The only FALSE result is returned in cell A5, where the result of both tests (whether B5 equals 3 or is less than 10) is FALSE, returning the FALSE result (a 0).
You can use a nested IF statement as the “value_if_true” argument in the same way. To do this, type =IF(first_test, IF(second_test, value_if_true, value_if_false), value_if_false).
As an example, if cell B3 contains the number 3, and if cell C3 contains the number 4, return a 5. If B3 contains a 3, but C3 doesn’t contain a 4, return a 0.
If B3 doesn’t contain a 3, return the number 1 instead.
The results of this example show that, for the first test to be true, cell B3 has to equal the number 3.
From there, the “value_if_true” for the initial IF uses a second, nested IF statement to make a second test (whether C3, C4, C5, or C6 contain the number 4). This gives you two potential “value_if_false” results (a 0 or a 1). This is the case for cells A4 and A5.
If you don’t include a FALSE argument for the first test, Google Sheets will return an automatic FALSE text value for you instead. This is shown in cell A6 in the above example.
Using IF with AND and OR
As the IF function performs logical tests, with TRUE or FALSE results, it’s possible to nest other logical functions like AND and OR into an IF formula. This allows you to run an initial test with multiple criteria.
The AND function requires all test criteria to be correct for a TRUE result to be shown. OR requires only one of the test criteria to be correct for a TRUE result.
To use IF AND, type =IF(AND(AND Argument 1, AND Argument 2), value_if_true, value_if_false). Replace the AND arguments with your own, and add as many as you like.
To use IF OR, =IF(OR(OR Argument 1, OR Argument 2), value_if_true, value_if_false). Replace and add as many OR arguments as you require.
This example shows IF AND and IF OR being used to test the same values in the B and C columns.
For IF AND, B3 must equal 1 and C3 must be less than 5 for A3 to return a “Yes” text string. Both results are TRUE for A3, with one or both results FALSE for cells A4 and A5.
For IF OR, only one of these tests (B3 equals 1 or C3 less than 5) has to be TRUE. In this instance, both A8 and A9 return a TRUE result (“Yes”) as one or both results in columns B and C are correct. Only A10, with two failed results, returns the FALSE result.
