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, IFS allows you to test more than one condition without nesting. This makes makes formulas with many conditions easier to read.
Arguments are entered in test/value pairs. Each test (condition) represents a logical test that returns TRUE or FALSE, and each value is associated with the previous test. A value is returned by IFS only when its test returns TRUE, and the first test with a TRUE result "wins". The IFS function supports up to 127 conditions.
In the example shown, the formula in E5, copied down, is:
The IF function can perform a logical test and return 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 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 lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.