A Nested IF formula in Excel helps you test different conditions within one formula, allowing you to make several decisions based on your data. This article explains how Excel’s Nested IF function can solve complex choices easily, making it a useful tool for handling many conditions without mistakes. In Excel, you can use the functions:
- IF
- IFS
- VLOOKUP or XLOOKUP
#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
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
Building nested IF is unnecessary in this situation 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.
