Getting Jiggier with IF. Utilizing AND / OR to increase the functionality of IF

 Are you ready to bring your IF functions to the next level?!? 

=IF(user response = "YES", Continue Reading, Revisit the post on IF functions)

Hopefully if you're reading this post you can follow along with my terrible IF joke. If you have no idea what I'm talking about or just need a refresher on how to use the IF function take a look this post here. 

By itself, the IF function is quite powerful.With it we can seamlessly apply logic to our excel spreadsheets and create dynamic workbooks that are sure to impress your coworkers, boss, their boss, etc. But what if you needed to evaluate multiple conditions? What if these all needed to be TRUE? What if just one of these needed to be TRUE?  This is where the AND / OR functions come into play

What do these functions do

AND / OR are both logic functions that are used to connect two or more expressions. The evaluated expressions can then be passed on to a larger more comprehensive formula

• AND - Checks whether or not all arguments result in TRUE
  
• OR    - Checks whether or not any of the arguments result in TRUE

 

Why are these functions important

AND / OR extends the logic used in an Excel formula allowing us to evaluate multiple expressions rather than just a single expression. This greatly extends the capabilities of the IF function. 

 

How do we use these functions

Lets start with the arguments

• =AND(logical 1, logical 2,...) 
• =OR(logical 1, logical 2,...) 

           AND / OR support a total of 255 arguments. Logical[1-255] are any conditions that can result in either TRUE or FALSE.

 

Quick examples

=AND(1=1 , A=B)

Result: False

=OR(1=1 , A=B)

Result: True 

 

 

How are these used with an IF function 

 Now that we got the basics out of the way, lets go into examples of how to use AND / OR with IF. 

Suppose you were given a list of users. This data contains name, age, gender, and favorite animal 

 

You are given the following assignment. Identify all users between the ages of 30 and 39. Mark these users as belonging to "Campaign 1", all other users should be assigned to "Campaign 2"

The assigned task can be done in excel through a two part formula containing both an IF and AND function. Lets take a look at what that formula would look like, first starting with the AND portion.

AND
= AND([AGE] >=30,[AGE] <=39)
This function looks at a users age and evaluates if its both greater than or equal to 30 and less than or equal to 39. Any user between the age of 30 and 39 will result in a TRUE. We can now grab that formula and enter it into our IF function to get the final result

=IF(AND([AGE] >=30,[AGE] <=39), "Campaign 1" , "Campaign 2")

 After applying the formula to our spreadsheet this end result

After the success of your assignment, management has requested your help on a new task. This time around they would like you to identify all users who's favorite animal is either a cat or a dog and to  include that user in the CAT DOG Newsletter. Users who are not included in that list should not be part of any newsletter 

Again, the assigned task can be done in a two part formula but this time with an IF and an OR. Lets take a look at what this formula would like like, starting with the OR

=OR([Favorite Animal] = "Cat",[Favorite Animal] = "Dog")

This function look at the users favorite animal and evaluates if that animal is either a cat or a dog. Any user who's favorite animal is a cat or a dog will result in a TRUE.  We can now enter that into our IF function 

=IF(OR([Favorite Animal] = "Cat",[Favorite Animal] = "Dog"),"CAT DOG Newsletter","")

 After applying the formula to our workbook here is our result

 

Getting Jiggy with IF. Working with IF functions in Excel

 

What is the IF function

The IF function allows a user to make comparisons based on a defined logic, returning different results based on whether or not the expression is true or false. 

 

Why is the IF function important

The key to building dynamic spreadsheets is the IF function. The function is incredibly versatile and can be used for everything from aggregation to data manipulation.

 

 

How do you use an IF function

Lets start with the components of the function. An IF formula is written as:

=IF(Logical_Test,[Value if true],[Value if false])

 

There are three arguments in the IF function

1 Logical Test - This is the condition we're testing for. This consists of a value or expression that can be evaluatied as true or false (ex: 1 = 2, FALSE)

2 Value if true - This is the formula output if the  condition is true. By default TRUE is returned if this is left blank

3 Value if false - This is the formula output if the condition is false. By default False is returned if this is left blank

 

Quick examples

=IF(1=2,"Green","Red")

Result: Red

 

=IF(1<2,"Green","Red")

Result: Green

 

Pretty simple to follow, right? Lets dive into a more complex example

 

 Suppose you had to identify which cells were numbers in the following set of data.

 

The goal is to mark anything that’s non-numeric with a "Y" to indicate that the row should be ignored.

 

To express this as an IF formula in cell C2 we can use 

=IF(ISTEXT(A2),"Y","N")

 

If we were to click and drag to apply to all cells the results would be

 

 

Have a good understanding of the IF function? Ready to take your IF knowledge to the next level? IF so check out our Getting Jiggier with IF post to learn about how to use AND / OR with the IF function

Quick Tip! - Finding Duplicates in your Data

You're giving the following data and are assigned with finding duplicate email addresses. If you're like me, lazy and don't want to manually go through rows of data, the quickest method of finding duplicates is by using conditional formatting. Here's how we do that

 

Step 1 - Highlight the column we would like to search for duplicates in, in this case the Email column. 

 

Step 2 - In the home tab, navigate to "Conditional Formatting" → "Highlight Cell Rules" → "Duplicate Values..."

 

Step 3 - A dialog box will appear, select the appropriate items in the drop down. In this example we are leaving the selections as the default

At this point the duplicated data will be highlighted. In our small data set this should be enough to identify all our duplicates however there may be times when you are working with data containing hundreds or thousands of rows. In that case we would want to filter the data

Filtering the data - Navigate to the data tab and select filter. Drop downs should appear at the header row of your data. Select the drop down over email and navigate to "Filter by Color" → "Filter by Cell Color". Select pink

Now the only rows visible will contain your duplicate values.
 

Troubleshooting VLookups

WHY ISNT THIS VLOOKUP WORKING?!?! 

Troubleshooting your formulas can be annoying. Here are common reasons you can have an error

 

 The formula result is not the expected value

• Range Lookup is set to true  When range lookup is set to true, excel will return the first result from the closest match. Check if your formula is set to True, 1, or is left blank. If so try updating the last parameter to False
  
• There may be duplicates values in your in your lookup column. A VLookup will return the first match in the data table. Check your data and ensure the values in the first column of your data table are unique. Check out our post on Finding Duplcates for more info.

 

 The formula result is #REF!

Excel will throw a #REF! error when a cell or range that is being referenced is not valid. In the case of a Vlookup, the #REF! error is typically caused when the result column number is greater than the number of columns found in the data table. For example, the image below has a data table containing two columns of data.

If we tried using the following formula,  =VLOOKUP("1001",A:B,3,FALSE),  Excel would throw a #REF! error because the data table does not contain three columns.

 The formula result is #N/A

We saved the best for last and of course by best I mean the error most likely to have you pounding on your keyboard! An #N/A error will be thrown when a match is not found. This can be caused by a number of reasons so lets dive into them

• The value being looked up is not in your lookup column. This one's a bit obvious but the frist step is to check the data you are looking exists in your table.
  
• There are trailing or leading spaces in your data. Sometimes data can have extra spaces which can cause unexpected behavior when writing formulas. A quick check would be to click on the applicable cell in the data table and highlight the value in the formula bar. If the highlight extends past (or before) the text then we know the problem is spaces. 
  
  
  
  To fix we can either clear the spaces out of the text in our data table or modify the VLookup to include the spaces. In the example above we would change to =VLOOKUP("1001     ",A:B,2,FALSE) to normalize with our data
  
  
  
• The data you are you looking up is stored as a different type. Excel stores each value in a cell as the appropriate data type. 123 would be stored as a number while ABC would be stored as a text. Sometimes however depending on the structure of the data we can have a mismatch between the value in the cell and its data type. Most commonly this is the case with numbers that act as a dimension (eg: ID, Invoice Number, Reference Number, etc). When doing a VLookup we want to make sure that the lookup value is in the same data type as the values in the lookup column.
  
  Example: the ID column is stored as text. This is identifiable by the green triangles in the upper left column. Our VLookup in cell I1 is attempting to run the find value 1001 instead of "1001". Because no number 1001 exists in the table, the formula is returning #N/A
  
  
  To fix we can either wrap the number in quotes and update the formula to =VLOOKUP("1001",A:B,2,FALSE). 
  

Writing Your First Vlookup

What is a Vlookup?

A Vlookup is a function allow a user to pull in a result from a specified column of a data table based on search value.

Why Should I learn about Vlookup's?

Vlookup's are one of the most commonly used excel formulas. Whether you're just starting your career or are a seasoned professional, chances are having an understanding of the vlookup will be crucial for your day to day excel work. It can be the difference of spending hours manually finding values in a spreadsheet vs seconds of writing a formula and having excel do the work for you. Also, Vlookup's are one of the simplest formulas to learn and apply in practice. Learning the basics of writing the Vlookup formula will help build a foundation in which you can apply to more advanced formulas. Exciting stuff, right!?

Enough of the boring stuff, how do we use a Vlookup?

To use a VLookup we'll need to first identify the components of the formula, also known as the arguments. The Vlookup Formula is written the following.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The formula consists of 4 arguments (separated by commas), which can be broken down as:

1. Lookup Value
   The value we are looking up. This value should exist in the first column of the to be searched data table
2. Table_Array
   The range containing the data
3. Col_index_num
   The column number we would like to return as the result
4. Range_lookup (optional)
   
   Either true or false. Tells excel to bring in the first closest match (True) or to find an exact match (False). In most situations this should be false but excel will default to true by default.
   

To get a better idea of how the formula works, let's revisit the image at the header of the post. The data contains a list of US States with their Capital City and population count.

For example, if we would like to lookup the capital city of California the VLookup formula would consist of the following

1. Lookup Value
"California", or cell reference F1 which contains the value California
2. Table_Array
The relevant data in our workbook starts at cell A2 and ends C7. For table_array we will use A2:C7
3. Col_index_num
Capital is the second column in our data table. 2 will be our Col_index_num
4. Col_index_num
We would like to find an exact match of our lookup value. False will be our range_lookup

This formula will return "Sacramento" 

Having trouble getting your VLookup to work. Check out our post on Troubleshooting Vlookup's