Some of the first functions that a newly-titled “power user” learn are **IF **and **VLOOKUP**. The **IF** function provides the user the ability to ask a question and perform action “*A*” for one answer and action “*B*” for another answer. The catch is that any question posed must be answerable as either “*true*” or “*false*”.

A simple example would be something like, “*Are the contents of cell A1 greater than the contents of cell B1?*” If the answer is “*yes*”, calculate a 10% bonus; if not, then no bonus is offered. This **IF** (sometimes referred to as a “*simple IF*”) has the built-in limitation of only being able to perform a single evaluation. What does one do if several evaluations need to take place?

### Enter the Nested IF

The **nested IF** is simply an **IF** inside of an **IF,** which could then be inside another **IF**. Excel’s **IF** has a limit of 64 nested levels. This provides the user with up to 65 possible actions to take.

*(SIDE NOTE: It is often suggested that if you are writing more than 4 or 5 levels in a nested IF, you should really think about using a different function to accomplish your task, such as VLOOKUP.)*

Let’s take the following example of determining letter grades based on test scores. The below table shows the ranges that grades fall into.

Assuming we name the cell containing the final score “*score*”, the formula to calculate the letter grade would look as follows:

**=IF(Score>89,”A”,IF(SCORE>79,”B”,IF(Score>69,”C”,IF(Score>59,”D”,”F”))))**

This formula is only determining five (5) possible outcomes, yet it suffers from several problems.

- Each function asks a variation on the same question about the same source data
- The length of the formula is becoming excessive
- The opportunity to make a typo increases exponentially (or geometrically; whatever. It’s big!) the longer the formula becomes.
- The formula becomes increasingly difficult to keep track of parenthetical pairing.

### EXCEL 2016 and New Functions Save the Day

Excel 2016 is equipped with two new functions called **IFS** and **SWITCH** that help mitigate all of the above problems associated with multi-level **nested IF** statements.

Using our current example of number grades to letter grades, we can greatly reduce the length and repetition of the original **nested IF** by using a new function called **IFS**. The new **IFS** function strips out the need for creating a new **IF** statement for each new evaluation. Below is the same formula but accomplished with an **IFS** function.

**=IFS(Score>89,”A”,Score>79,”B”,Score>69,”C”,Score>59,”D”,TRUE,”F”)**

The new **IFS** function is not doing anything more or less than the original **nested IF**, but it performs the task more elegantly due to the removal of the redundant **IF** functions.

Notice there is only a *SINGLE* set of parentheses instead of four, or 64 in the worst case scenario.

*(SIDE NOTE: The IFS function supports 127 separate evaluations, compared to 64 evaluations when nesting older IF function.)*

### Let’s Flip the Question

Suppose you know what the letter grades are but you need to discover the test score ranges.

The function to use in this scenario is called **SWITCH**. The **SWITCH** function is similar to the **IFS** function except that it simply looks for an instance of data in a list and makes a corresponding offer of new data.

**=SWITCH(A2,”A”,”90-100″,”B”,”80-89″,”C”,”70-79″,”D”,”60-69″,”F”,”0-59″)**

Think of it as a **VLOOKUP** with the table built right into the formula.

In the **SWITCH** function, a value (or expression) is compared to items in a list. The first matching item in the list will then return a result. Because **SWITCH** halts the evaluation when it encounters a matching value, it is recommended that the most likely matching item(s) be placed first in the list. This will reduce the amount of unnecessary comparisons.

## Another Use for SWITCH

How about this example?

You have a spreadsheet with a column of dates and you want to determine if a date is occurring today, yesterday, tomorrow, or in the near future or near past. We could write a formula like the following to calculate the number of days between the defined day and today.

**=DAYS(TODAY(),A1)**

This would produce the following table.

By nesting the above function inside of a **SWITCH** function, we can convert those numbers into words.

**=SWITCH(DAYS(TODAY(),A1),0,”Today”,1,”Yesterday”,-1,”Tomorrow”, “Out of Range”)**

Splash a bit of **Conditional Formatting** color on the list and you now have something like the following.

Sort the list by date in descending order and you have the result below.

## Let’s Pair them Up for a Super Function

If we nest the **SWITCH** function inside the **IFS** function, we can make it a bit more sophisticated.

The **SWITCH** function is simply looking for matching items. Suppose we want to introduce a bit of comparison logic into the mix. Let’s say that if an item is greater than one (1) day in the future it is considered “*Pending*”, and if it is greater than one (1) day in the past it is considered “*Processed*”. The days between those points in time are where we want the “*Yesterday/Today/Tomorrow*” logic.

The **IFS** function will determine the “*Pending/Processed*” status while the **SWITCH** will determine the “*Yesterday/Today/Tomorrow*” status

**=IFS(DAYS(TODAY(),A1)>1,”Processed”,DAYS(TODAY(),A1)<-1,”Pending”,TRUE, SWITCH(DAYS(TODAY(),A1),0,”Today”,1,”Yesterday”,-1,”Tomorrow”))**