Nested IF allows you to create complex IF functions in Excel. But also, Microsoft has built new functions to avoid creating very long IF formulae.

Methods for making nested IF

There are 5 methods to perform nested IF in Excel

• Several IF in the same formula
• The VLOOKUP function (option approximate match)
• The XLOOKUP function
• The IFS function (Excel 2016 and more)
• The SWITCH function (Excel 2016 and more)

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

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.

Write Several IF

But if you have to build a test that doesn't follow a range of values, you must use another technique.

How to build a nested 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.

Example with 3 results

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

• 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"))

Example with 4 results

And now, if we also apply the seniority for the men, the formula is more complex

=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

IFS Function

As you can see, the more you have tested, the longest 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
• ....

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")

With an IFS function, if the first logical tests are FALSE, the only way to always perform the latest test is to write TRUE as the last logical test.