Using the Excel IFS Function

The IFS function works just like a nested IF.  You can use it to examine multiple conditions. The difference between the IFS function and a nested IF is, the IFS function is simpler to use and easier to understand because you do not need to nest one IF function inside another IF function.  The IFS function is available in Excel 16 and higher.

Syntax:

=IFS(condition,value to return if TRUE,condition,value to return if TRUE,condition,value to return if TRUE, …)

Example 1: You want to check cell B1; and, if it is equal to 1, you want Excel to return 100. If it is equal to 2, you want Excel to return 200. If it is equal to 3, you want Excel to return 300.

=IFS(B1=1,100,B1=2,200,B1=3,300)

This formula works fine with one major exception. If B1 is not equal to 1, 2, or 3, the formula returns #N/A.

To resolve this, set a condition of TRUE and specify the value to return. This way, if the value is anything other than 1, 2, or 3, Excel will return the value associated with the condition TRUE.

Example 2: For example, you want to check cell B2; and, if it is equal to 1, you want Excel to return 100. If it is equal to 2, you want Excel to return 200. If it is equal to 3, you want Excel to return 300. If it is anything else, you want Excel to return "Try Again."

=IFS(B2=1,100,B2=2,200,B2=3,300,TRUE,"Try Again")

The AND Function

A logical is an expression that evaluates to either TRUE or FALSE. The AND function checks one or more logicals. If they are all true, it returns TRUE; otherwise, it returns FALSE.

The syntax:

=AND(logical 1, logical 2, …)

Examples:

Function	Result
=AND(1=1, 2=2)	TRUE
=AND(1=1, 2=3)	FALSE

The AND function is useful when you are working with the IF function and you want to include a range of values.

Example 3: You want to find out if the value in cell B3 is less than 100 and greater than 74. Enter the following formula:

=AND(B3<100,B3>74)

If B3 is less than 100, the first logical will return TRUE. If B3 is greater than 74, the second logical will return TRUE. If both logicals are TRUE, the function will return TRUE. Only values between 100 and 74 will return TRUE. In other words, 100 will not return TRUE and 74 will not return TRUE.

Example 4: Now, you have enough information to solve the following problem. You have a person who is going to receive a score between 100 and 0. If they receive a score of 100, they receive $1000; if they receive a score between 100 and 74, they receive $800.00; if they receive a score between 75 and 49, they receive $600; if they receive a score of less than 50, they receive $400. Their score is in cell B4. Enter the following formula to determine the amount they receive:

=IFS(B4=100,1000, AND(B4<100,B4>74)=TRUE, 800, AND(B4<75,B4>49)=TRUE,600, AND(B4<50, B4>-1), 400,

TRUE,"Invalid Entry")