Build a nested IF in Excel

Build a nested IF in Excel
Last Updated on 25/02/2024
Reading time: 2 minutes

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.

Nested IF in Excel

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

First part of the nested IF

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

Nested IF with 3 situations

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

Nested IF with 4 Result

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

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

IFS function with 4 tests

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

VLOOKUP returns the result for any values

Leave a Reply

Your email address will not be published. Required fields are marked *

Build a nested IF in Excel

Reading time: 2 minutes
Last Updated on 25/02/2024

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.

Nested IF in Excel

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

First part of the nested IF

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

Nested IF with 3 situations

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

Nested IF with 4 Result

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

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

IFS function with 4 tests

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

VLOOKUP returns the result for any values

Leave a Reply

Your email address will not be published. Required fields are marked *