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.
