The Excel IFS function runs multiple tests and returns a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas.
Test multiple conditions, return first true
Value corresponding with first TRUE result
=IFS (test1, value1, [test2, value2], ...)
test1 - First logical test.
value1 - Result when test1 is TRUE.
test2, value2 - [optional] Second test/value pair.
IFS is a new function, available in Office 365 and Excel 2019.
Use the IFS function to test multiple conditions and return a value corresponding to the first TRUE result. Unlike the IF function, The IFS function can test multiple conditions at the same time without nesting multiple IF statements. Formulas based on IFS are therefore shorter and easier to read and write.
Conditions are entered in test/value pairs. Each test represents a logical test that returns TRUE or FALSE, and the value that follows will be returned when the result is TRUE. For example, an IFS formula with 3 tests can be visualized like this:
Notice in this case conditions are arranged to test higher values first.
Example #3 - default value
The IFS function does not have a built-in default value to use when all conditions are FALSE. However, to provide a default value, you can enter TRUE as a final test, followed by a value to use as a default.
In the example below, a status code of 100 is "OK", a code of 200 is "Warning", and a code of 300 is "Error". Any other code value is invalid, so TRUE is provided as the final test, and "Invalid" is provided as a "default" value.
When the value in A1 is 100, 200, or 300, IFS will return the messages shown above. When A1 contains any other value (including when A1 is empty) IFS will return "Invalid". Without this final condition, IFS will return #N/A when a code is not recognized.
The IFS function does not have a built-in default value to use when all conditions are FALSE.
To provide a default value, enter TRUE as a final test, and a value to return when no other conditions are met.
All logical tests must return TRUE or FALSE. Any other result will cause IFS to return a #VALUE! error.
If no logical tests return TRUE, IFS will return the #N/A error.
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can...
VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...