**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 you have logical tests, 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**

- Logical test 1 => result when test is TRUE
- Logical test 2 => result when test is TRUE
- Logical test 3 => result when 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

If you want to **return a value inside a range of numbers**, the functions VLOOKUP or better XLOOKUP do the job. In the following example, you return the percentage according to the number of cars sold.