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.
