 # Build a nested IF in Excel #### Frédéric LE GUEN

Last Updated on 14/09/2023

Nested IF allows you to create complex selections of data with Excel

1. Write a formula with several IF

Many IF, VLOOKUP, XLOOKUP, IFS and SWITCH

2. Use the new IFS function

This function allows to write many conditions instead of one with the IF function

3. 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

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

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

## #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.

# Build a nested IF in Excel

Last Updated on 14/09/2023

Nested IF allows you to create complex selections of data with Excel

1. Write a formula with several IF

Many IF, VLOOKUP, XLOOKUP, IFS and SWITCH

2. Use the new IFS function

This function allows to write many conditions instead of one with the IF function

3. 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

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

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