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 (1 ^{st} logical test, 2^{nd} logical test)**

In our case, for the AND function:

- 1
^{st}logical test = Check if the Age is equal (‘=’) or above (‘>’) 62 - 2
^{nd}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 (1 ^{st} logical test, 2^{nd} logical test)**

In our case, for the OR function:

- 1
^{st}logical test = Check if the Country is equal to the “US” - 2
^{nd}logical test = Check if the Country is equal to “Canada” - If
**neither**the 1^{st}**nor**the 2^{nd}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(**1 ^{st} logical_test**,

**1**,IF(

^{st}value_if_true**2**,

^{nd}logical_test**2**

^{nd}**value_if_true**,

**value_if_false**))

**1**: Check if the Age is equal (‘=’) or above (‘>’)^{st}logical_test**60**, and the country is “**US**“**1**: Display “YES” if the age is actually equal (‘=’) or above (‘>’) 60, and the country is “US”^{st}value_if_true**2**: Check if the Age is equal (‘=’) or above (‘>’)^{nd}logical_test**62**, and the country is “**Canada**“**2**^{nd}**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:

Name | Country |

Jon | US |

Maria | US |

Bill | Canada |

Rene | Canada |

Lea | Canada |

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_value, table_array, col_index_num, [range_lookup])**

**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)**table_array****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**[range_lookup]**can assume either a ‘TRUE’ value for an approximate match, or a ‘FALSE’ value for an exact match

You can learn more about VLOOKUP, by checking this tutorial about VLOOKUP functions in Excel.

(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).

To learn more about the different functions that you may perform in Excel, feel free to visit our Excel tutorials for additional step-by-step guides.