How to use the IF function in Google Sheets

1. IF Function

Figure 1.1 Conditional Statement

1.1 Conditional Statement

         Conditional statements are a way for us to automate tasks that involve decisions. Most of the scenarios that we’ll encounter in the spreadsheets will involve some sort of testing or checking conditions. A lot of calculations and other processes require criteria that needs to be satisfied in order for the processes to proceed. This process of testing a condition and executing a task based on the results of that test is one of the building blocks of most functions and formulas, and there’s one function that embodies this structure – the IF function.

1.2 What does the IF do in Google Sheets?

         The IF function tests a condition and returns a value based on the result of that test (Fig. 1.1). We can set unique values or nest other functions for each of the Boolean results (i.e., TRUE and FALSE). 

1.3 IF Syntax

Below is the syntax for IF:

There are three inputs in the IF function: logical_expression, value_if_true, value_if_false. The logical_expression is the condition that will be checked for TRUE or FALSE. The value_if_true is the value that will be returned if the condition is satisfied while the value_if_false is the value that will be returned if the condition is not met. Let’s take a look at a basic example (Fig. 1.2) to help us build an intuition on how the formula works. 

         The task in the scenario below is to output 0.5 if the input is less than 1 and to output 1 if the value is anything but less than 1 (Fig. 1.2). The input is written in A2 and the output is in B2. If A2 < 1, then the output will be 0.5 no matter how lesser than  one the input is. Now, the interesting part is when the condition is not satisfied. The important thing to remember when doing conditional statements is that the other range of values in the spectrum of the input can be anything. This means that if we input a letter in A2, the IF function will return 1 because a letter is not less than 1. Therefore, depending on the scenario, we may need to consider other ranges of values that an input can accept. For now, this simple scenario here is enough to display the power of the IF function.  

Figure 1.2 Basic example of IF Process

  1. Breaking Down the Formula

Figure 2.1 IF Function

In this section, we’ll talk about the rules and criteria for each IF input, how to write them, how IF processes these inputs and how do the inputs affect the output. We’ll build an intuition to make it easier for us to visualize and create an IF formula for your problems.

2.1 IF Input/Arguments

2.1.1 Logical_Expression

The logical_expression is the condition, which is basically an equality or inequality, that will be checked in order to decide what values to return. We can divide the logical_expression into two parts: 

  1. Value1, Value2: The values that we want to check against each other. 
  2. The Logical Operators: equal sign (=), not equal to (<>), less than (<), greater than (>), less than or equal to (<=) and greater than or equal to (>=). 

The logical_expression, which is shown below, from Fig. 1.2 illustrates the aforementioned two parts. A2 serves as the value1 while the number 1 is the value2. The logical operator is less than (<). These are the necessary terms that complete the inequality, which returns TRUE or FALSE depending on the value of A2. 

=IF(A2<1,0.5,1)

This is the typical example of the syntax of the logical_expression, but we can also omit the logical operator and value2. The question is what will be the range of values the value1 can have in this scenario? You may think that since there’s no logical operator and no value2, the value1 must be a boolean value (i.e., TRUE or FALSE). That’s not the complete answer. It turns out that the value1 if it’s on its own can be any numerical value. We take the essence of this and simplify it to an expression:

=IF(A2,TRUE, False) is equivalent to

=IF(A2<>0,TRUE, False)

Therefore, if A2 is a number and is not 0, then the IF function will treat it as TRUE. Otherwise, if it’s 0, then it’s equivalent to FALSE. Note that a blank or empty cell and empty string (“”) are considered as 0 here. If A2 is a text, then the IF function will return the #VALUE! Error.

Here are the important rules that you need to consider for the Value1 and Value2:

2.1.1.1 Syntax and Location of the Value1 and Value2

In Google Sheets, the values for the logical_expression can be written or coded in four ways depending on the scenario:

  1. Reference to a cell: 

Typically, our data set is stored in a sheet, and we want to be able to use this set of data in our formulas. In order to do that, we write the references of the cells where the data set is located. In the formula above, the reference A2 means that the value is in column A, row 2. The reference defines the address of the cell, and the formula uses the reference to access the value within the referenced cell. 

The main advantage of referencing the cells is that the formula becomes dynamic. We can change the values within the referenced cells, and the formula will update automatically in response to the changes. Both value1 and value2 can be referenced from cells at the same time.

We can write the reference in the formula or highlight the cell. If we perform the latter, the spreadsheet automatically writes the references for us (Fig. 2.2), so we don’t have to write the references manually.

Figure 2.2 Referencing cells by highlighting them

Also, it’s important to know that we can refer to the cells from any sheet. This means that the cells can be:

  1. in the same sheet with the function
  2. in  another sheet within the same workbook
  3. Or in another sheet in another workbook (using ImportRange function)
  1. A Constant value:

Both values can be hard-coded as constants. The normal usage would be one is a referenced cell while the other is a constant just like the previous example from Fig. 1.2. 

  1. Value generated from another Function

The values can also be generated from other functions. Here’s an example:

Figure 2.3 Value1 from the LEFT function

  1. Array and Range of Cells

The first three methods define the basic property of value1 and value2 in terms of its size. Normally, both values are single value inputs, and therefore, arrays and ranges of cells are not accepted as inputs. To evaluate an array or a range of cells, we need a special method: the ArrayFormula function. The value1 in the examples below is the array or the range of cells. 

Range of Cells:

=ArrayFormula(IF(B2:B6=DATEVALUE(“1/1/2021”),ROW(B2:B6),))

Hard-Coded Arrays:

=ArrayFormula(IF({“Red”,”Green”,”Yellow”}=”Red”,TRUE,FALSE))

Arrays from another function:

=ArrayFormula(IF(LEFT(A2:A6,2)=”XX”,ROW(B2:B6),))

For now, we can think of an array as a list of values that are not located in any sheet. To write them directly, we need to write the list inside the curly brackets {} (see hard-coded formula).

An array or a range of cells for the values will not work in an ordinary IF formula. We need to use the ArrayFormula function in order for this to work correctly. We can write the ArrayFormula function and put the IF inside, or once you’re done writing the IF formula, you can just press CTRL+SHIFT+ENTER, and Google Sheets will enclose the IF with the ArrayFormula function. 

2.1.1.2 Dimension and Size of Value1 and Value2

In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D). 

The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value. 

The size of the data is related to the number of rows and columns, which also translate to the number of entries. 

Both values can be 1D or 2D:

  1. One-Dimensional (1D):
    1. Horizontal List: 
  • Cells (Fig. 2.4)

Figure 2.4 1D Value1: one row, multiple columns

  • Hard-coded Arrays: (values in a horizontal list are separated by commas)

=ArrayFormula(IF(LEFT({“XX-1001″,”XX-1002″,”YY-1002″},2)=”XX”,TRUE,FALSE))

The array here is the same as the range B1:D1 in Fig. 2.4. Also, notice the ArrayFormula function. 

  1. Vertical List: 
  • Cells (Fig. 2.5)

Figure 2.5 1D Value1: one column, multiple rows

  • Hard-coded Arrays: (values in a vertical list are separated by semicolons)

=ArrayFormula(IF(LEFT({“XX-1001″;”XX-1002″;”YY-1002″},2)=”XX”,TRUE,FALSE))

  1. Two-Dimensional (2D):
  • Cells: (Fig. 2.6)

Figure 2.6 2D Value1: Multiple Rows and Columns

  • Hard-Coded Arrays: (the array below is a 2 (row) by 2 (column) array)

=ArrayFormula(IF(LEFT({“XX-1001″,”XX-1002″;”YY-1001″,”YY-1002″},2)=”XX”,TRUE,FALSE))

2.1.1.3 Data Type of Value1 and Value2

If the logical_expression is complete (i.e, value1, logical operator and value2), then both values can be any data type. If the logical_expression only consists of value1, then the value1 must be numeric values (e.g., numbers, boolean, etc.). Boolean values, which are TRUE and FALSE, have equivalent numeric values, which are 1 and 0, respectively. 

2.1.2 Return Values: Value_If_True and Value_If_False

Both inputs have the same rules and properties. One of the great things about the IF function in Google Sheets is that we can set these two inputs as blank. This means that we can omit their values and the result will be empty cells, which are different from an empty string (“”). 

Except for the special case of the logical_expression, all inputs of the IF function have the same rules and properties. Therefore, we just need to apply the same rules and properties from the value1 and value2 to the return values. 

2.2 The IF Process

2.2.1 How does the IF work?

We already had a glimpse of how it works from section 1.3, and in this section we’ll look at more detailed examples of how the IF processes its inputs.

2.2.1.1 IF (Basic)

The task in the scenario below is to provide the amount in Column C based on the description in Column B (Fig. 2.7). Let’s explain how the IF function works using a flowchart (Fig. 2.8). In B2, the condition B2 = “COMPLETE” is checked first. If the condition is satisfied, which means the condition is TRUE, then the process will flow from the checking of condition to the result of the test. For B2, the condition is true (see column E). Therefore, the process will now flow to the value_if_true, which is 1,000. If the condition is not met like B4, then the process will proceed to the value_if_false, which is 500. There are always two possible outputs for the IF function.

Figure 2.7 IF: Basic Process

Figure 2.8 IF: Flowchart for the scenario in Fig. 2.7

As discussed previously, the logical_expression can consist of the value1 only, and this special case has an effect on its data type. You may think that this case is useless, but actually it’s a powerful property that is used in array formulas. You’ll find out later that the AND and OR functions don’t work well in array formulas. Therefore, the alternative is using this special property of the logical_expression. 

2.2.1.2 IF (Array and Range of Cells)

The scenarios in this section require the use of the ArrayFormula function in order for the IF function to work. You can either write the IF function inside the ArrayFormula function or press CTRL+SHIFT+ENTER after you write the IF function, which automatically encloses the IF function with the ArrayFormula function. So how do we know if we need to use the ArrayFormula?

We’ve discussed in the previous sections the basic methods of writing the inputs. These methods are related to the necessary properties of the inputs like the dimensions, locations,  data types and so on. We loosely define arrays as lists of values that are not located in  the sheets. This means the lists are not physically stored  in the cells in any sheet. This definition is deliberate so that we can distinguish it from a range of cells. If we use an array or a range of cells as an input to functions that normally don’t accept arrays or ranges of cells as inputs, then we need to use the ArrayFormula function in order to evaluate the inputs, but not all inputs can work this way. Some inputs are strict when it comes to its properties. Even if you use the ArrayFormula function, it will not work and will only return an error. 

Both three inputs of the IF function can be arrays or ranges of cells through the ArrayFormula function. How do the IF function process these inputs? Let’s look at the different examples below. 

  1. 1D Logical_Expression: One of the values is an array or a range of cell

Let’s take a look back at the scenario from Fig. 2.7, and instead of using a formula for each of the cells, we’ll use one array formula that will generate the same output data (Fig. 2.9). The IF function starts with the first cell, which is B2. The same process applies here but to a range of cells. The value1 is a vertical list, and therefore, the output of the IF function also follows the same dimension, arrangement and size, but how does the IF function process the list? Let’s look at array operations and how array inputs interact with each other. 

Figure 2.9 1D Value1

The intuition is based on the layout of the spreadsheet. Using this intuition, let’s build visualizations and rules that will aid us in constructing the formulas. Let’s imagine the inputs as cells in spreadsheets, and arrange them like their arrangement in the formula, which is a vertical list for value1 and single values for the other inputs (Fig. 2.10). Next, let’s combine the inputs into one matrix by pairing them. The pairing is a one-to-one correspondence, which means that row 1 of value1 pairs with row 1 of value2, value_if_true and value_If_FALSE and so on, but there’s a problem in the size of the inputs. We can’t pair the inputs because they have different sizes (i.e., value1 is a 1D array while the other inputs are single values). To be able to pair them in a one-to-one correspondence, the dimensions, sizes, and arrangements of all the inputs must be the same. So how do we go about this problem? Let’s try to imagine adjusting the dimensions, sizes and arrangements until all of them are the same. We’ll adjust the dimension based on the input that has the largest dimension, which in this case is value1 (1D). For the size of row and column dimensions, we’ll do the same. To adjust, we duplicate the single values until they match the input with the largest dimension and size (Fig. 2.10). Now that all of the inputs have the same dimension, size and arrangement, we can now pair them into one matrix. 

This thought process here is just a way for us to visualize how the spreadsheet would evaluate the inputs, but it does not represent the actual process or what the spreadsheet is really doing in the background. We are just looking at the essence of how it works using an analogy that works for us.

Figure 2.10 Matrix of Inputs: 1D Value1 vs. Single Value Inputs (Value2, Value_If_TRUE, Value_If_FALSE)

The IF function will follow the matrix of inputs above. It will start with the first row, evaluate the inputs and return the corresponding output. Then, it will evaluate the next row and so on. The final output data will follow the dimension, size and arrangement of the matrix of inputs. 

  1. 1D Logical_Expression: Value1 and Value2 are both 1D Lists

What if both value1 and value2 are 1D arrays or ranges of cells? How do we arrange both inputs in the formula? If value1 is a vertical list, do we also set value2 as a vertical list like the example below (Fig. 2.11)? Or is it a horizontal list? Let’s delve in deeper to establish the rules that we need to follow when it comes to array operations. 

The task now is to separate the codes with YY and ZZ into separate columns. We’ll assign 1,000 in the amount if the condition is met and blank if the condition is false. 

Figure 2.11 1D Value1 (Vertical) and 1D Value2 (Vertical)

Let’s follow the steps that we did earlier. Let’s imagine that the inputs are stored in cells and pair them to produce the matrix of inputs for this problem (Fig. 2.12). We can see that there’s a bit of a problem with adjusting the dimensions, sizes and arrangements, especially for value2. Value2 is a 1D vertical list, and therefore, we just need to adjust its size. The problem is that duplicating its values doesn’t make sense. If we analyze the task, what we need is to conduct a separate testing of conditions for each of the letter codes. One for YY and one for ZZ. If we just duplicate the values until its size matches the size of value1, then some rows in value1 will be tested for YY only while the others are only tested for ZZ. The spreadsheet doesn’t work that way. If you look at Fig. 2.12, it does not duplicate the values and leave the other rows of value2 empty. Therefore, if you look at the final matrix of inputs, the last three rows have empty value2s, and if you look back at the results in Fig. 2.11, the matrix clearly explains why there are #N/A Errors in the last three rows. The conclusion here is that if there are two or more 1D lists and they have different sizes (not single values), then they can’t have the same arrangement. If that is the case, then one of them must be vertical and the other is horizontal. 

Figure 2.12 Matrix of Inputs: 1D Value1 (Vertical), 1D Value2 (Vertical) vs. Single Value Inputs (Value_If_TRUE, Value_If_FALSE)

Let’s apply the conclusion that we formulated above. Let’s arrange value2 into a horizontal list. Then, let’s adjust the dimensions and sizes. Value1 has the largest row size and value2 has the largest column size. Therefore, we’ll adjust all of the inputs based on these two. The result will be a 2D pairing (Fig. 2.13). The important thing here is that it’s always a one-to-one correspondence. Therefore, row 1, column 1 (1, 1) of value1 pairs with (1,1) of value2 and the other inputs. Row 1, column 2 (1, 2) of value1 pairs with (1, 2) of the other inputs. Then, move to the next row and so on. The resulting matrix of inputs will be a 2D array with a size that is equivalent to the row size of value1 and column size of value2. 

The IF function will follow the resulting matrix of inputs starting with the inputs in (1, 1) to (1, 2) and then the next row and so on. See the output data in Fig. 2.14.

Figure 2.13 Matrix of Inputs: 1D Value1 (Vertical), 1D Value2 (Horizontal) vs. Single Value Inputs (Value_If_TRUE, Value_If_FALSE)

Figure 2.14 1D Value1 (Vertical) and 1D Value2 (Horizontal)

  1. 1D and 2D Combinations:

We’ve seen the array operations for the logical_expressions only. If the value_if_true and the value_if_false are also 1D lists, then the same processes apply. If all of the inputs have the same dimensions, size and arrangement, then there’s no problem in pairing them, but if not, just follow the same steps. Adjust the sizes and dimensions based on the inputs that have the largest row and column sizes. Note that the restrictions for the adjustments also apply. 

All of the formulated rules apply to 2D inputs. If all are 2D, then make sure that all inputs have the same sizes. If there are 1D and 2D combinations, make sure that the one of the dimensions (i.e., row and column) of the 2D input are the same with the dimensions of the 1D inputs, and the 2D inputs must have the same sizes. Let’s look back at the example in Fig. 2.14, and instead of a 1D horizontal list for value2, let’s make it a 2D list (Fig. 2.15).

Value2 is a 5 by 2 array. The comma represents the division of the column while the semicolon is for rows. The result of the array formula is the same as the result in Fig. 2.14. Notice that the row dimension of the 2D value2 is the same with the row dimension of the 1D value1 (both have 5 rows). Let’s again imagine the inputs in cells again (Fig. 2.16). 

Following the same steps, we adjust the other inputs to match the value2, and this will look like the 2nd image in Fig. 2.13, which will proceed to the same input matrix. Therefore, the output data are still the same. 

Figure 2.15 1D Value1 (Vertical) and 2D Value2 (5 rows, 2 columns)

Figure 2.16 1D Value1 (Vertical), 2D Value2 (5 rows, 2 columns) and other inputs (single values)

2.2.2 Is the IF Function Case-Sensitive?

The IF function is not case-sensitive. You can perform conditions with different combinations of upper and lower cases, and the IF function will be able to identify the same characters (Fig. 2.17).

Figure 2.17 IF is not Case-Sensitive

2.2.3 Does the IF Function support Wildcards?

Wildcards (i.e., *, ? and ~) don’t work in IF formulas. Therefore, we can’t perform partial matches using this method, but there are ways to perform partial matches other than using wildcards (see Chapter 3). 

2.3 IF Output

2.3.1 Dimension and Size of the Output

We’ve seen in section 2.2.1.2 that the dimension, size and arrangement of the output of the IF function will be dependent on the same properties of its inputs. For array results, just make sure that the destination cells of the outputs are vacant cells. If there are values that are occupying the cells where the results should be displayed, then an error will be returned instead of the results (Fig. 2.18). 

Figure 2.18 The IF function returns a #REF error due to D4 blocking the results

2.3.2 Data Type of the Output

The data type of the output will be the same as with the data type of the value_if_true or value_if_false.  

  1. Applications of IF

In this section, we’ll tackle how to formulate your own IF for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the IF function. We just need to follow the rules, and we’ll be able to create the IF formula that we want.

3.1 How to formulate an IF Formula 

  1. First we need to identify the inputs for our IF:
  1. Logical_Expression: It’s better to start determining the locations of the value1 and value2 and what logical operator to use. How many criteria? Do we need to use arrays? What should be the dimensions, size and arrangements?
  1. Value_If_TRUE, Value_If_FALSE: Next, we need to determine how to pair these two inputs with the locical_expression. Do we also create an array list? How about the dimensions, size and arrangements?
  1. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as inputs for the IF function.  Most of the time, we don’t have the control on the arrangement and values of our raw data. With this step, we can carefully follow the rules for each of the inputs and see if we need to take some preprocessing steps before we can apply the inputs to the function. 

  1. Setting up the IF Formula:

Step 1 and Step 2 will give us an idea how to arrange the inputs of the functions and the format of the resulting output. It’s always better to visualize the inputs when constructing the formula. This will help us predict the dimension, size and arrangement of the results. Just remember that if the output is an array, we need to make sure that there are vacant cells for them to fill up. Otherwise, an error will be returned instead of the output. 

  1. Steps to write the formula:
  1.  In a cell where you want to write the formula, write =IF(

As you write the formula, Google Sheets will display the formulas that match what you’re typing (Fig. 3.1). You can then select from the options that are displayed by pressing the TAB key,  or just complete the formula by writing it manually. 

Figure 3.1 Writing the IF function

  1. Next, after the open parenthesis (, write the value1 or highlight the cell reference if the value1 is stored in a cell. If you highlight the cell, the spreadsheet will automatically write the reference for you. Then, write the logical operator and do the same for the value2 (Fig. 3.2).

Figure 3.2 Logical_Expression

  1. Write a comma to separate the first input, and then, write or highlight the value_if_true and value_if_false (Fig. 3.3). 
  2. We can write the close parenthesis or just hit ENTER, which will automatically complete the formula for us. 

Figure 3.3 Value_If_TRUE and Value_If_FALSE

3.2 How to do IF with Different Data Types

All of the data types in Google Sheets have their own set of formatting. The formatting of values is just a way of displaying the values such as percentages, decimals and so on, but Google Sheets doesn’t necessarily use the exact values that are displayed in the cells. Therefore, we need to take into consideration the data type that we are working with. Let’s look at different examples. 

  1. How to do IF with Numbers and Texts

The goal of the formula below is to divide Input 1 by Input 2 (Fig. 3.4), but we want to prevent the error due to division by 0. Therefore, we use the IF function to check if Input 2 is greater than 0. The value_if_true is the formula that executes the division. The value_if_false is a warning message that tells the user that the input is incorrect and what value should be the input. If the condition is met, the formula is executed. Otherwise, if the condition is FALSE, then the warning message is returned instead. The important thing here is to observe how we write texts and numbers as constants in a formula. 

The important part is that the logical_expression of the IF function is strict when it comes to data types. One of the prerequisites to satisfy the condition is that the data type of the value1 and value2 must be the same. Numbers must be written as numbers (no quotation marks). Texts must always be enclosed with quotation marks. Ensure that both value1 and value2 of the logical_expression have the same data type. 

Figure 3.4 IF: Value1 and Value2 are both numbers

If 0 is written as text, we won’t get the result that we want (Fig. 3.5). Therefore, we need to be careful with the data types. 

Figure 3.5 IF: Value1 is a number while  Value2 is a text

  1. How to do IF with Dates 

Dates are treated as numerical values. Although we see this common date format (i.e., dd/mm/YYYY) displayed in the cells, the spreadsheet does not use this in its evaluation. Instead, it operates using date serials. The dates have equivalent date serials, which are the number of days from a referenced date (i.e., 12/31/1899 = 1). If you highlight the Dates and select Automatic as the Format, you’ll see that the dates will be converted to their corresponding date serials. These are the numbers that will be evaluated by the functions.

It’s easier to reference a date from a cell because when we write dates in cells, Google Sheets can recognize a lot of date formats such as Jan-01-2021, January 1, 2021 or 1/1/2021 as dates. Therefore, we don’t need to think about the date serials when referencing the cells, but if the dates will be hard-coded as constants in the formula, then we need to convert the dates into their date serials. Below are some of the ways to convert the hard-coded dates to their corresponding date serials:

Using DATEVALUE function:

=IF(A2>=DATEVALUE(“1/1/2021″),”TRUE”,”FALSE”)

Using DATE function:

=IF(A2>=DATE(2021,1,1),”TRUE”,”FALSE”)

3.3 How to do IF with Blank or Empty Cells

The IF formula below checks if A2 is empty using the empty string (“”) value. To check for blank or empty cells, we need to use the quotation marks with no space in between (“”). This is called an empty string. Remember that for texts, we must enclose them with quotation marks, and if we write nothing inside the quotation marks, then it’s an empty string. This is equivalent to a blank (Note: The space is counted as a character. ). See the formula in the formula bar in Fig. 3.6.

Figure 3.6 IF with Blank or Empty Cells: Empty String (“”)

Another method to check for blank cells is by using the ISBLANK function (Fig. 3.7). It takes one input and checks if the input is blank. If true, the ISBLANK function returns TRUE. Otherwise, it will return false. 

Figure 3.7 IF with Blank or Empty Cells: ISBLANK

3.4 How to do IF with Non-Blank or NonEmpty Cells

If we want to use the non-blank value for the logical_expression, we just need to use the not equal to (<>):

=IF(A2<>””, “Not Blank”,”Blank”)

Or we can also use the negative of the ISBLANK using the NOT function:

=IF(NOT(ISBLANK(A2)), “Not Blank”,”Blank”)

3.5 How to do IF with Partial Match

We can’t perform partial matches in IF formulas using the wildcards, but we can use the SEARCH function and FIND function as substitute for the wildcards. Although both functions perform similar tasks, they have some major differences when it comes to their processes. One main difference is that the FIND function is case-sensitive while the SEARCH function is not. Let’s look at some of the examples on how to utilize these functions together with the IF function. 

The SEARCH function has two main inputs. The first input is the lookup value and the second input is the cell or text where we want to search the lookup value. The SEARCH will find the lookup value and will return its position within the text. Otherwise, if the lookup value is not within the cell or the given text, the #VALUE! Error is returned instead. We use the ISERROR function to check if the SEARCH function will return an error, which means that there’s no match (Fig. 3.8). 

Figure 3.8 IF Partial Match using SEARCH Function

The FIND function works the same way, but it is case-sensitive:

Figure 3.9 IF Partial Match using FIND Function

3.6 How to do IF with Multiple Criteria

Before we move on to the examples, let’s elaborate the meaning of multiple criteria in this section. It’s important to understand that the logical_expression can only handle one condition or one set of conditions. Therefore, the multiple criteria here pertains to the criteria within one set. The logical_expression can handle multiple criteria with the aid of two functions: AND and OR. For basic IF formulas, the two functions are very useful for one set of multiple criteria with one set of corresponding return values. For array formulas, we need to do a different method. Let’s look at the examples. 

3.6.1 IF with Multiple Criteria: OR

  1. Basic IF Formula

The task below is to calculate the sum of the two amounts from January and February if either of them contains a value greater than 0 (Fig. 3.10). Otherwise, if both months don’t satisfy the set of conditions, then return a -100. We can use the OR function to enclose the conditions for this problem. The OR function returns TRUE if at least one condition is satisfied. If all conditions are not satisfied, then the OR function will return FALSE. 

Figure 3.10 IF with OR Function

  1. Array IF Formula

We can also perform the task above using one array formula that will output all of the results in Column D. The problem is that the OR function can’t perform array operations. Therefore, if we input an array or a range of cells in the OR function, it will evaluate all of them and will only return a single output, but what we need is to do an OR test for each entry in the array or range of cells. 

In order to do this, we’ll create a logical_expression based on array operations and utilize the special case for the logical_expression (Fig. 3.11). Both conditions are represented by their array forms as shown in the formula below. Also, notice the addition of the two. This is necessary to perform the equivalent of the OR function. The value_if_true is also in array form while the value_if_false is a single value. Let’s look how the Array OR works. 

The (B2:B4>0) is for January and is the first condition. The second condition is (C2:C4), which is for February. Both expressions will return TRUE if the condition is satisfied and FALSE if not. These are represented in Column G and F from Fig. 3.12. TRUE and FALSE have equivalent numerical values, which are 1 and 0, respectively. Therefore, we can use boolean values in arithmetic operations. If we add the results of the two boolean arrays, we’ll have values that are whole numbers (i.e., 0, 1, 2, …). If the result of the addition is 0, then both conditions are not met (Column H, Fig. 3.12). If the result is 1, then one of the conditions is satisfied, and 2 means that both conditions are satisfied. Therefore, for the Array OR, 1 and 2 means TRUE while 0 is false. The two arrays are combined into one array of whole numbers. Therefore, the logical_expression now only consists of value1s from Column H. If we remember the special case for the logical_expression (i.e., logical_expression consists only of value1), value1=0 means FALSE and value1<> 0 is TRUE (Note that value1 must be a number). Therefore, the combination of the array addition and this special property will replicate the OR function for each of the values in the arrays. 

Figure 3.11 IF with Array OR

Figure 3.12 Array OR

3.6.2 IF with Multiple Criteria: AND

  1. Basic IF Formula

In the example below, there are two conditions that must be satisfied (Fig. 3.13). The first condition is that the January Column must be 0 while the second condition is that the February Column must be greater than 0. If both of these conditions are true, then the amount from February Column is returned. Otherwise, if at least one of the conditions is false, then 0 is returned. We use the AND function to do this. If all conditions are satisfied, the AND function will return TRUE.  If at least one is false, then the AND function will return FALSE. 

Figure 3.13 IF with AND Function

  1. Array IF Formula

Just like the OR function, the AND function can’t perform array operations. Therefore, we’ll also do the same trick here. For Array Or, we used addition while for the Array AND, we’ll use multiplication (Fig. 3.14). Multiplying the boolean arrays will only result to an array of 1s and 0s (Fig. 3.15). 1 means that both conditions are satisfied while 0 means that either both conditions are false or one of the conditions is not met. 

Figure 3.14 IF with Array AND

Figure 3.15 Array AND

3.7 IF vs. IFS: Nested Conditional Statements

We’ve talked already about one set of conditions from the previous section, but what if there are multiple sets of criteria or conditions? This also means that for every set, there’s a set of return values (i.e., value_if_true, value_if_FASLE). For this scenario, we’ll nest multiple IF functions, where each of them represents a set of conditions. Let’s look at an example. 

  1. Nested IF Functions

The task below is to determine the appropriate discount based on the amount (Fig. 3.16). The criteria for the discount is listed in table E1:F4. There are three ranges of values with corresponding discounts. This means that these three criteria will be the three sets of logical_expressions. To nest a function, we just need to write the function as an input to the main function. In the case of the IF function, we just need to write another IF function as the value_if_true or the Value_if_False or both. We can also write an IF function as the logical_expression, but for this scenario, we just need to look at the conditions and their corresponding return values if they are TRUE. We need to determine the order of which sets of conditions must be checked first. Normally, we just follow the ascending (or descending) order for numbers, but we can create any order as long as the flow of the logic makes sense. Let’s look at the flowchart that represents the nested IF formula for this problem (Fig. 3.17).

Figure 3.16 Nested IF formulas

The IF function starts with the first condition: B2<1000 (Fig. 3.17). If this condition is true, then return 5. Otherwise, we move on to the value_if_false, which is another IF function that represents that next condition: AND(B2>=1000, B2<2000). If the AND function returns true, then we return its corresponding discount, which is 10. Otherwise, if the AND returns false, then we move on to the value_if_false of the second IF function. Since the last condition is greater than and equal to 2000, which has no upper limit, we can write it’s corresponding discount in the value_if_false. We can interpret the last condition as “others,” which  means any value that did not satisfy the first two will end up in this category. Therefore, we don’t need to add another IF function. 

Figure 3.17 Nested IF Flowchart

  1. IFS Function

Another way to solve the nested conditional statements above is to use the IFS function (Fig. 3.18). It works similar to the IF function but for multiple sets of conditions. The IFS function can accept several sets of conditions but only has one return value for each set. Let’s apply the IFS function to the problem in Fig. 3.16. 

The IFS function starts with the first condition: B2<1000. If this condition is true, then the corresponding return value, which is 5, is returned. Otherwise, it will move on to the next condition and so on. Notice that the last condition is set to TRUE. There’s no condition to check since the value is TRUE already. Therefore, if the conditions prior to the last condition are not satisfied, then a value of 15 is returned. 

The IFS function is more convenient and concise compared to the nested IF functions, especially for larger sets of conditions. We can easily read the conditions and their return values. Therefore, it’s easier to debug if there will be errors. It’s also easier to expand if we want to add more conditions. 

Figure 3.18 The IFS Function

3.8 How to do IF with Inputs in Another Sheet

We can have inputs of IF from another sheet. The process of constructing the formula is still the same. We just need to reference the inputs from another sheet. 

3.8.1 IF with Inputs in Another Sheet (same Workbook)

The task is to label each of the codes as complete or incomplete based on their corresponding amounts (Fig. 3.19). The label will be in another sheet, which is the IF Sheet (Fig. 3.20). The criterion is >1500 for “COMPLETE.” If this is not satisfied, the value “INCOMPLETE” is returned instead.

Figure 3.19 The data set is located in a different sheet (Data Sheet) 

Here are the steps:

  1. Write = IF(
  1. Highlight (or write) the value1 and value2 for the logical_expression:

We can’t simply write B2>1500 because value1 is now from a different sheet. We need to reference the cells together with the reference sheet. The fastest way to do this is to highlight the cells. While the cursor is inside the IF function, we go to the Data Sheet tab. Then, we highlight the cell B2, the spreadsheet automatically writes the cell reference including its sheet reference (Fig. 3.21). The sheet reference always starts with the sheet name followed by an exclamation mark (!). Then, this is followed by the cell reference. Complete the logical_expression. 

  1. Write a comma, and then, go back to the where the destination sheet and write the return values (Fig. 3.20). Then, press ENTER. 

Figure 3.20 IF with inputs from a Different Sheet in the same Workbook

Figure 3.21 Highlighting the Cells from a different Sheet

3.8.2 IF with Inputs from Another Sheet (in another Workbook)

If the inputs are located from another workbook, then we need to use the IMPORTRANGE function in order to pull the data from another workbook. The IMPORTRANGE requires two inputs: the URL of the workbook and the sheet and cell references. Both inputs for the IMPORTRANGE must be in text format. Therefore, we need to enclose the URL and reference with quotation marks. IMPORTRANGE requires permission to pull the data from another workbook. Once access is granted, it will automatically pull the data. 

Below is an example on how to apply the IMPORTRANGE to IF:

=IF(IMPORTRANGE("https://docs.google.com/spreadsheets/Example","Data!B2>1500"),”COMPLETE”,”INCOMPLETE”)