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

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

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

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

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