The IFS function was officially introduced with Excel 2019, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013 and 2016 versions. It works exactly as its original Microsoft counterpart.
Description
The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. It is designed to replace complex, nested IF statements, making your formulas much easier to read, build, and maintain.
Before IFS existed, to test multiple conditions you had to write formulas like this:
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))
With IFS, the same logic is written in a simple, linear format:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"D")
This is particularly useful for:
- Grading systems: Assigning letter grades based on score ranges
- Categorization: Grouping data into multiple categories based on conditions
- Data cleansing: Mapping values to standardized outputs
- Complex business logic: Handling multiple decision branches without the complexity of nested
IFstatements
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
You can include up to 127 condition/result pairs.
Examples
Example 1: Letter Grades
To assign grades based on scores (A: >=90, B: >=80, C: >=70, D: >=60, F: <60):
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")
The formula checks each condition in order and returns the grade for the first one that is TRUE.
Example 2: State Lookup
If you have cities in column A and want to return the corresponding state:
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Brisbane","QLD", TRUE,"Not Found")
If none of the listed cities match, the final TRUE condition returns "Not Found".
Example 3: School Year to Age Group
To classify students by school year:
=IFS(A1<=2, "Under 11", A1<=4, "Under 13", A1<=6, "Under 15", TRUE, "15+")
This formula checks the school year number in A1 and returns the appropriate age group.
Example 4: Multiple City Mapping
For mapping multiple communities to their city:
=IFS(OR(C27={"Boykin Hills","Collins Cove","Night Harbor"}),"Chapin", OR(C27={"Bradford Meadows","Canopy of Oaks","Jackson Preserve"}),"Sumter", TRUE,"")
This uses OR with arrays to check if the community belongs to a city.
IFS vs Nested IF
The “Catch-All” Default Value
One important difference from the traditional IF function is that IFS does not have a built-in value_if_false argument.
To specify a default result when none of your conditions are met, add a final TRUE as your last logical test:
=IFS(condition1, result1, condition2, result2, TRUE, default_result)
If you don’t include a TRUE catch-all and none of the conditions evaluate to TRUE, the function will return an #N/A error.
Important Notes
- ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2019 and Microsoft 365.
- ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
- ⚠️ Order matters – Conditions are evaluated sequentially, and the function stops at the first
TRUEcondition. Test from highest to lowest (or lowest to highest) to avoid unexpected results. - ℹ️ Text values in the result arguments must be enclosed in double quotation marks.
- ⚠️ If a logical test resolves to a value other than
TRUEorFALSE, the function returns a#VALUE!error. - ℹ️ While you can use up to 127 conditions, performance may degrade with many tests, and alternative methods like
VLOOKUPwith a reference table might be more efficient.