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

 

NameAgeSenior Discount
Jon62
Maria51
Bill60
Rene65
Lea59

 

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):

 

Guide to an IF Function in Excel 2016

 

(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

 

Guide to an IF Function in Excel 2016

 

(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:

 

Guide to an IF Function in Excel 2016

(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

 

NameAgeCountrySenior Discount
Jon62US
Maria51US
Bill60Canada
Rene65Canada
Lea59Canada

 

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):

 

IF Function in Excel 2016

 

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

 

IF Function in Excel 2016 - AND and OR

 

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:

 

IF Function in Excel 2016

 

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”

 

NameAgeCountrySenior Discount
Jon62BrazilNO

(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

 

NameAgeCountrySenior Discount
Jon62US
Maria51US
Bill60Canada
Rene65Canada
Lea59Canada

 

Here are the steps:

(1) Copy the above table into Excel (into the cells of A1 to D6):

 

Excel table

 

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

 

Executing IF within an IF

 

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:

 

Executing IF within an IF in Excel

(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:

NameAge
Jon62
Maria51
Bill60
Rene65
Lea59

 

While Sheet2 contains the Name and Country:

NameCountry
JonUS
MariaUS
BillCanada
ReneCanada
LeaCanada

 

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):

 

Excel example

 

(2) Copy the second table into Sheet2 (into the cells of A1 to B6):

 

Excel table

 

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

 

Using VLOOKUP within an IF function

 

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

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:

 

Using VLOOKUP within an IF function

(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

 

NameAgeCountrySenior Discount
Jon62USYES
Maria51USNO
Bill60CanadaNO
Rene65CanadaYES
Lea59CanadaNO

 

Let’s see the steps:

(1) First, copy the above table into Excel (into the cells of A1 to D6):

 

Table in Excel 2016

 

(2) Select the cells D2 to D6 (you’ll notice that a green frame surrounds your selection of those cells):

 

Excel 2016

 

(3) Click on ‘Conditional Formatting’ located under the Home tab:

 

Conditional Formatting in Excel

 

(4) Select “Highlight Cells Rules”, then choose “Equal To…”

 

Conditional Formatting in Excel 2016

 

(5) Type NO (without quotations) within the input box:

 

Conditional Formatting in Excel

 

(6) Press ‘OK’ and you’ll now see that all the cells that contain ‘NO’ are highlighted in red:

 

Use Conditional Formatting in Excel 2016

 

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

 

Example of Conditional Formatting in Excel

 

(8) Click on ‘Conditional Formatting’ located under the Home tab:

 

Conditional Formatting in Excel 2016

 

(9) Select “Highlight Cells Rules”, then choose “Equal To…”

 

Example of Conditional Formatting in Excel

 

(10) Type YES (without quotations) within the input box:

 

Use Conditional Formatting in Excel

 

(11) Pick the option of ‘Green Fill with Dark Green Text’ from the drop-down list:

 

Example of Conditional Formatting in Excel 2016

 

(12) Press ‘OK’ and you’ll now see that all the cells that contain ‘YES’ are highlighted in green:

 

Full example of Conditional Formatting in Excel

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.