Condition

Nested IF in Excel

Reading Time: 3 minutes

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.

VLOOKUP returns the result for any values

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.

Nested IF in Excel

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

=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

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

Nested IF with 4 Result

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

IFS function with 4 tests

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.

Related posts

Calculate between two values

Frédéric LE GUEN

Wildcard with COUNTIFS or SUMIFS

Frédéric LE GUEN

Function IF – Examples with payments

Frédéric LE GUEN

Leave a Comment