IFS formula in Excel 2016

New Contributor

Hello all! 


I have created a spreadsheet that contains multiple IFS formulas. They work perfect in Office 365 Excel, Google sheets, etc. However, they seem to return a #NAME in Excel 2016. 


Does anyone know how I can resolve this issue?


=@IFS(E4>89, 5, E4>69, 4, E4>39, 3, E4>0, 2) is one example of the formula that functions properly in everything EXCEPT Excel 2016.


Thanks in advance!

3 Replies


IFS was introduced in Excel 2019, so it should be avoided in Excel 2016 and earlier.

You can change

=IFS(condition1, value1, condition2, value2, condition3, value3)


=IF(condition1, value1, IF(condition2, value2, IF(condition3, value3)))

and similar for more or fewer conditions.

Hello @Hans Vogelaar ,

Thank you for the quick reply!
Im not sure I fully understand how to separate them.

I tried: =IF(E4>89, 5), IF(E4>69, 4), IF(E4>39, 3),IF(E4>0, 2) but get a #value return.

Would it be possible to help with this example?
=@IFS(E4>89, 5, E4>69, 4, E4>39, 3, E4>0, 2)

Thanks in advance!

best response confirmed by miniding89 (New Contributor)


That would be


=IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, IF(E4>0, 2))))


If E4 will never contain a number <=0, you can shorten it to


=IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, 2)))