How to Use the IFS Function in Excel

The IFS function is a logical function that allows you to test multiple conditions and return a value based on the first condition that is true. It is similar to the IF function, but it can handle more than one condition without nesting.

The syntax of the IFS function is:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

• logical_test1 is the first condition to test.
• value_if_true1 is the value to return if the first condition is true.
• logical_test2, value_if_true2, etc. are optional additional conditions and values to test and return, up to 127 pairs.
• The IFS function will evaluate the conditions in order, and return the first value that matches a true condition.
• If none of the conditions are true, the IFS function will return an #N/A error.

Step-by-Step Guide

To use the IFS function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type = and then IFS( to start the function.
3. Enter the first logical test, followed by a comma. For example, A2>90,
4. Enter the value to return if the first logical test is true, followed by a comma. For example, "A",
5. Repeat steps 3 and 4 for each additional condition and value pair, up to 127 pairs. For example, A2>80,"B",A2>70,"C",A2>60,"D",
6. Close the parenthesis and press Enter. For example, =IFS(A2>90,"A",A2>80,"B",A2>70,"C",A2>60,"D")
7. Copy and paste the formula to other cells as needed.

Example

Suppose you have a list of students and their scores in an exam, and you want to assign grades based on the following criteria:

• A score of 90 or above is an A
• A score of 80 to 89 is a B
• A score of 70 to 79 is a C
• A score of 60 to 69 is a D
• A score below 60 is an F

You can use the IFS function to create a formula that will return the corresponding grade for each student. Here is how the formula would look like in cell B2:

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")

Note that the last condition is TRUE, which means that if none of the previous conditions are true, the function will return “F”. This is a way to handle the default case when using the IFS function.

Here is how the formula would work for the sample data:

How to Use the IFS Function in Excel: Other Approaches

The IFS function is a convenient way to test multiple conditions and return values, but it is not the only way. Here are some other approaches that you can use in Excel:

• The IF function: This is the basic logical function that tests one condition and returns a value based on whether it is true or false. You can nest multiple IF functions to handle more than one condition, but this can make the formula complex and prone to errors. For example, the formula in cell B2 using nested IF functions would be:

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

• The CHOOSE function: This is a lookup function that returns a value from a list based on a given index number. You can use the CHOOSE function with a combination of other functions, such as MATCH, to test multiple conditions and return values. For example, the formula in cell B2 using the CHOOSE function would be:

=CHOOSE(MATCH(A2,{0,60,70,80,90},1),"F","D","C","B","A")

• The VLOOKUP function: This is another lookup function that returns a value from a table based on a given lookup value and column index. You can use the VLOOKUP function with a table that contains the criteria and the values to return. For example, the formula in cell B2 using the VLOOKUP function would be:

=VLOOKUP(A2,$D$2:$E$6,2,TRUE)

Where D2:E6 is a table that contains the criteria and the values to return, such as: