The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. The IFS function works in Excel 2019.
Formula:
= IFS([Logical test1, Value if True1, Logical test2, Value if True2, Logical test3, Value if True3)
Please note that IFS function allows maximum 127 different conditions.
Example:
Students test scores are marked from 0 to 100. The scores need to be converted into A, B, C, D and F with the following rules:
– scores that are greater than 89 are converted to A;
– scores that are greater than 79 are converted to B;
– scores that are greater than 69 are converted to C;
– scores that are greater than 59 are converted to D; and
– scores that are less than 60 are converted to F.
In the Cell B2, type the formula below and copy down. All scores will be converted to letters.
=IFS(A1>89,"A",A1>79,"B",A1>69,"C",A1>59,"D",A1<60,"F")