Excel IFS Function

General Formula

=IFS(test1,value1,[test2,value2],...)

Overview

Excel IFS function is designed to run many tests after which it returns values that are matching the first result that is TRUE in the logical arguments. You can as well use the Excel IFS function when assessing many conditions without a multiple IF nested statements. The IFS function gives room for a shorter and easy to read formula.

Use of the Excel IFS function

Used to test several conditions and returns a value that matches the first true result

Generated Value (Result) for the Excel IFS Function

A value that is corresponding to the first ‘TRUE’ result

Parameters used in the Excel IFS Function

  • Value1 – A matching result that is given when test1 appears to be TRUE.
  • Test1 – This is the leading logical test.
  • Test2: This is the second logical test
  • Value2 – [optional] the second pair or value/test.

Example

Figure 1: Example showing how to use the Excel IFS function to return matching grade to the score criteria provided

The formula used in D5 is:

=IFS(C5<40,"F",C5<45,"E",C5<50,"D",C5<60,"C",C5<75,“B”,C5>=75,"A")

This returns an “A” as the grade matching the score ‘89’

Notes of Usage for the Excel IFS Function 

You can utilize the Excel IFS function when testing several conditions and then return a corresponding value matching the first result that is ‘TRUE’. One good thing about the Excel IFS function is the fact that it permits you to test several conditions without the problem of nesting, this is different from the IF function. This property of the Excel IFS function makes using formulas with multiple conditions very easy to read through.

With the IFS function, pairs of values and test arguments can be entered. Each of these test (criteria) is a representation of one logical test which will return a TRUE or a FALSE value, and each of these value is still associated with the previous or initial test. When using the IFS function, a result is returned only when its logical test returns ‘TRUE,’ and the initial logical test having a TRUE test result is declared to wins. With the Excel IFS function, about 127 conditions can be supported.

General Notes about the Excel IFS function

  • The default cannot be set when all tests values return as FALSE- value if it’s a false result. Instead, you have to put TRUE for the final logical test, and then you can enter a value that will be returned as a default value if the test result is FALSE.
  • Every logical tests used must either return a TRUE or a FALSE result. Other results different from these will cause the Excel IFS function to return #VALUE!.
  • Where there is not a single logical test that returns a TRUE result, Excel IFS function will automatically return a #N/A error message.

Note: The Excel IFS function is new only in Excel 2016 through Office 365.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar