02/12/2021
Condition

# Nested IF in Excel

Nested IF allows you to create complex IF functions. 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 appromiate 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 the 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

Now, for the FALSE argument, we write the second IF function for the test for the women and the seniority

### Example with 4 results

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

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