How to use the IFS function in excel step by step with an example?

It’s available for Excel 365, Excel 2021, Excel 2019, and Excel 2016

we are using the IFS function to check whether one or more conditions are met and return a value that corresponds to the first TRUE condition.

Instead of a nested IF function we can use IFS Function.

Generally, the syntax for the IFS function is:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

In another way we can say

Syntax

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

Please note that the IFS function allows you to test up to 127 different conditions. However, we don’t recommend nesting too many conditions with IF or IFS statements. This is because multiple conditions need to be entered in the correct order, and can be very difficult to build, test and update.

Now we can refer below examples to get an idea how the IFS function working in excel.

Example-1

If you refer above image formula for cells A2:A6 is:

 =IFS(A2>89,”A”,A2>79,”B”,A2>69,”C”,A2>59,”D”,TRUE,”F”)

Which says IF(A2 is Greater Than 89, then return a “A”, IF A2 is Greater Than 79, then return a “B”, and so on and for all other values less than 59, return an “F”).

Example-2

f you refer above image formula for cells G2 is:

=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)

Which says IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2 equals 2, then return the value in cell D3, and so on, finally ending with the value in cell D8 if none of the other conditions are met).

You can also use IFS function to prepare a mark sheet in excel.

You May Also Like

By admin

Leave a Reply

Your email address will not be published.