**Nested IF allows you to create complex selections of data with Excel**

**Write a formula with several IF**Many IF, VLOOKUP, XLOOKUP, IFS and SWITCH

**Use the new IFS function**This function allows to write many conditions instead of one with the IF function

**In case of calculation between a range of values, VLOOKUP or XLOOKUP are recommended**Those 2 functions are simplest to write instead of using IF

Let's see when it's better for you to use one of these functions

## #1: Write Several IF

The IF function returns 2 results according to the result of a logic test. But what if we are looking to return more than 2 results? The method is to **write another IF formula for the FALSE argument**.

Let's say you want to give a gift to your employees according to gender and also seniority in the company.

- For the
**men**, you give them the**Gift 1** - For
**women with less than 5 years of seniority**, it's the**Gift 2** - And for the
**women with 5 years of seniority and more**, it's the**Gift 3**

So, first, we write a test for the men

=IF(C2="male","Gift 1")

Now, for the FALSE argument, we write the second IF function for the test for the women and the seniority

=IF(C2="male","Gift 1",IF(AND(C2="female",D2<5),"Gift 2","Gift 3"))

And now, if we also apply the seniority for the men, this time **we have 4 situations to manage**

=IF(AND(C2="male",D2<5),"Gift 1", IF(AND(C2="male",D2>5),"Gift 4",

IF(AND(C2="female",D2<5),"Gift 2","Gift 3")))

Remark: To split your formula, place the cursor on your formula and press the shortcut

Alt + Enter

## #2: The IFS Function

As you can see, the more logical tests you have, the longer the formula is. This is why Microsoft has released a new function to avoid nested IF. **This function is IFS,** and it's very simple to write.

But, you MUST trick the last argument. Whatever the number of logical tests you write, **the last one MUST be TRUE. **For that, if all the previous tests are FALSE, the last one will be TRUE 😉

- Logical test 1 => result when the test is TRUE
- Logical test 2 => result when the test is TRUE
- Logical test 3 => result when the test is TRUE
- ....
- TRUE => Your last argument must be TRUE to be sure to return one value

In our case, the writing of the IFS function is

=IFS(AND(C2="male",D2<5),"Gift 1", AND(C2="male",D2>5),"Gift 4",

AND(C2="female",D2<5),"Gift 2",TRUE,"Gift 3")

## #3: Search between values

In this situation, building nested IF is not necessary because Excel has 2 great functions. 😀👍

If you need to determine a value within a numerical range, you can utilize the VLOOKUP function or the more advanced XLOOKUP function. In the example below, we demonstrate how to calculate the bonus percentage for salespeople based on the number of cars they have sold.

## Related Articles

- Convert TRUE or FALSE to 1 or 0 in Excel
- Excel Training – Build a nested IF
- Logical Test – Beginner Level
- Logical Test – Intermediate Level
- Training Logical Test – AND and OR functions