# Guide to an IF Function in Excel 2016

In this guide, I’m going to show you how to apply an IF function in Excel 2016. Note that similar principles apply to previous versions of Excel.

In general, IF functions allow you to perform logical tests in Excel.

So what type of logical tests are we going to see in this guide?

We’ll review the following scenarios:

 Item # Scenarios of an IF function in Excel 2016 1 Applying logical arguments using ‘=’, ‘>’ and ‘<‘ 2 Using ‘AND’ and ‘OR’ 3 Executing IF within an IF 4 Using VLOOKUP within an IF function 5 Displaying colors based on conditions

But before we dive into the above exercises, let’s look at the general structure of an IF function.

## Structure of an IF function in Excel 2016

The IF function in Excel 2016 has the following structure:

=IF(logical_test, [value_if_true], [value_if_false])

Where:

• logical_test is the logic or condition specified by the user
• value_if_true is the value that will be displayed if the condition is met
• value_if_false is the value that will be displayed if the condition is not met

Note that the structure of the IF function in Excel 2016, also applies in previous versions of Excel.

In the next section, we are going to see how this structure can be used across the different scenarios…

## (1) Applying logical arguments using ‘=’, ‘>’ and ‘<‘

Let’s now look at a simple scenario, where we have a table in Excel that contains the names and ages of 5 people.

Our goal is to determine if a person is eligible for a senior discount based on the following rules:

• If the person’s age is equal or above 60, then the person would be eligible for the discount
• Otherwise, for an age below 60, the person would not be eligible for the discount

 Name Age Senior Discount Jon 62 Maria 51 Bill 60 Rene 65 Lea 59

To accomplish our goal, we can use the IF function structure that we saw before:

=IF(logical_test, [value_if_true], [value_if_false])

Here are the steps that you may follow:

(1) Copy the above table into Excel (into the cells of A1 to C6): (2) Double-click inside the cell C2, and then type/copy the following IF function:

`=IF(B2>=60,"YES","NO")`

• logical_test: Check if the Age (cell B2) is equal (‘=’) or above (‘>’) 60
• [value_if_true]:  Display “YES” if the age is actually equal (‘=’) or above (‘>’) 60
• [value_if_false]: Display “NO” if the age is below 60 (3) Drag the IF function all the way from cell C2 to cell C6, so that the logical test would apply across all the rows in your table. Here are the results that you’ll see: ## (2) Using ‘AND’ and ‘OR’

Let’s make things more interesting by adding a “Country” column into our table.

We’ll now enhance the rules as below:

• If the person’s age is equal or above 62 AND the person resides in the “US” OR “Canada,” then the person would be eligible for the discount
• Otherwise, the person would not be eligible for the discount

 Name Age Country Senior Discount Jon 62 US Maria 51 US Bill 60 Canada Rene 65 Canada Lea 59 Canada

To perform the above logical test, we can use a combination of AND and OR functions.

Here are the steps:

(1) Copy the above table into Excel (into the cells of A1 to D6): (2) Double-click inside the cell D2, and then type/copy the following IF function:

```=IF(AND(B2>=62,OR(C2="US",C2="Canada")),"YES","NO")
``` Let’s break down the usage of the AND and OR functions:

AND (1st logical test, 2nd logical test)

In our case, for the AND function:

• 1st logical test = Check if the Age is equal (‘=’) or above (‘>’) 62
• 2nd logical test = Check if the Country is either “US” or “Canada”
• Only if both of the logical tests are met, then display [value_if_true] which is “YES”

OR (1st logical test, 2nd logical test)

In our case, for the OR function:

• 1st logical test = Check if the Country is equal to the “US”
• 2nd logical test = Check if the Country is equal to “Canada”
• If neither the 1st nor the 2nd logical tests are met, then display [value_if_false] which is “NO”

(3) Drag the IF function all the way from cell D2 to cell D6. You’ll see the following results: Let’s say that Jon suddenly decided to move to Brazil. If you change the Country value to Brazil (in cell C2), then you’ll notice that the senior discount would change from YES to NO.

This happens, since the country is neither the “US” nor “Canada”

 Name Age Country Senior Discount Jon 62 Brazil NO

## (3) Executing IF within an IF

Let’s suppose that you want to apply the following logical tests:

• If the person’s age is equal or above 60 AND the person resides in the “US”, then the person would be eligible for the discount
• Otherwise, If the person’s age is equal or above 62 AND the person resides in “Canada”, then the person would be eligible for the discount
• Else, the person would not be eligible for the discount

 Name Age Country Senior Discount Jon 62 US Maria 51 US Bill 60 Canada Rene 65 Canada Lea 59 Canada

Here are the steps:

(1) Copy the above table into Excel (into the cells of A1 to D6): (2) Double-click inside the cell D2, and then type/copy the following IF function:

```=IF(AND(B2>=60,C2="US"),"YES",IF(AND(B2>=62,C2="Canada"),"YES","NO"))
``` Here is the general structure that we have used:

=IF(1st logical_test, 1st value_if_true,IF(2nd logical_test, 2nd value_if_truevalue_if_false))

• 1st logical_test: Check if the Age is equal (‘=’) or above (‘>’) 60, and the country is “US
• 1st value_if_true:  Display “YES” if the age is actually equal (‘=’) or above (‘>’) 60, and the country is “US”
• 2nd logical_test: Check if the Age is equal (‘=’) or above (‘>’) 62, and the country is “Canada
• 2nd value_if_true: Display “YES” if the age is actually equal (‘=’) or above (‘>’) 62, and the country is “Canada”
• value_if_false: Display “NO” if none of the conditions above are met

(3) Drag the IF function all the way from cell D2 to cell D6, and you’ll get: ## (4) Using VLOOKUP within an IF function

Imagine that you have two separate tables, which are located in two different Excel sheets:

Sheet1 contains the Name and Age:

 Name Age Jon 62 Maria 51 Bill 60 Rene 65 Lea 59

While Sheet2 contains the Name and Country:

You can use VLOOKUP to connect those two tables together (using the common column of ‘Name’ which appears in both of the tables/sheets).

The logical test to be executed is the same as the IF within an IF scenario you just saw:

• If the person’s age is equal or above 60 AND the person resides in the “US”, then the person would be eligible for the discount
• Otherwise, If the person’s age is equal or above 62 AND the person resides in “Canada”, then the person would be eligible for the discount
• Else, the person would not be eligible for the discount

And here are the steps:

(1) Copy the first table into Sheet1 (into the cells of A1 to B6): (2) Copy the second table into Sheet2 (into the cells of A1 to B6): (3) Go back to Sheet1, and then Double-click inside the cell C2. Then, type/copy the following IF function:

```=IF(AND(B2>=60,VLOOKUP(A2,Sheet2!A:B,2,0)="US"),"YES",IF(AND(B2>=62,VLOOKUP(A2,Sheet2!A:B,2,0)="Canada"),"YES","NO"))
``` You’ll notice that we used the VLOOKUP function within our logical tests.

The VLOOKUP function has the following structure:

=VLOOKUP(lookup_valuetable_array, col_index_num[range_lookup])

1. lookup_value is the value (i.e., the ‘Name’) that is present in the first table (in Sheet1), which you wish to find in the second sheet (in Sheet2)
2. table_array is the range of cells that contain the second table, where you want to find your lookup_value
3. col_index_num is the column position in your second table. For example, the value of ‘2’ represents the second column (i.e., the Country) in your table. The values under this column will be obtained when you apply the VLOOKUP
4. [range_lookup] can assume either a ‘TRUE’ value for an approximate match, or a ‘FALSE’ value for an exact match

(3) Next, drag the IF function (in Sheet1) all the way from cell C2 to cell C6, and you’ll see: ## (5) Displaying colors based on conditions

To display colors based on specified conditions, you may use Conditional Formatting in Excel.

Let’s say that you now want to execute the following logical tests:

• If the value under the Senior Discount column = “NO” then highlight that cell with a red color
• If the value under the Senior Discount column = “YES” then highlight that cell with a green color

 Name Age Country Senior Discount Jon 62 US YES Maria 51 US NO Bill 60 Canada NO Rene 65 Canada YES Lea 59 Canada NO

Let’s see the steps:

(1) First, copy the above table into Excel (into the cells of A1 to D6): (2) Select the cells D2 to D6 (you’ll notice that a green frame surrounds your selection of those cells): (3) Click on ‘Conditional Formatting’ located under the Home tab: (4) Select “Highlight Cells Rules”, then choose “Equal To…” (5) Type NO (without quotations) within the input box: (6) Press ‘OK’ and you’ll now see that all the cells that contain ‘NO’ are highlighted in red: (7) You’ll need to repeat some of the above steps for the ‘YES’ values. Specifically, Select the cells D2 to D6 (as before, a green frame would surround your selection of those cells): (8) Click on ‘Conditional Formatting’ located under the Home tab: (9) Select “Highlight Cells Rules”, then choose “Equal To…” (10) Type YES (without quotations) within the input box: (11) Pick the option of ‘Green Fill with Dark Green Text’ from the drop-down list: (12) Press ‘OK’ and you’ll now see that all the cells that contain ‘YES’ are highlighted in green: ## Conclusion – IF function in Excel 2016

We just saw multiple scenarios of applying an IF function in Excel 2016. As indicated earlier, you can apply the same concepts in previous versions of Excel.

The IF function in Excel 2016 is indeed a powerful way to execute logical tests. You can use a combination of functions within the IF function (such as AND, OR, VLOOKUP).