IFS function with multiple conditions per value

SergeiBaklan
Diamond Contributor
Oct 06, 2019
Sydwizz

IMHO, both

=IFS( B4<0.16, 1, B4<0.33, 2, B4<0.5, 3, B4<0.66, 4, B4<0.83, 5, TRUE, 6)

and

=IF( B4<0.16, 1, IF(B4<0.33, 2, IF(B4<0.5, 3, IF(B4<0.66, 4, IF(B4<0.83, 5, 6)))))

shall work, as well as

=LOOKUP(B4,{-1,0.16,0.33,0.5,0.66,0.83},{1,2,3,4,5,6})
- Sydwizz
  Copper Contributor
  Oct 06, 2019
  SergeiBaklan Both the functions worked great, thank you so much for the help! It seems I was also incorrect in the way I placed my parentheses in separating the conditions. Thank you!
  - SergeiBaklan
    Diamond Contributor
    Oct 06, 2019
    Sydwizz , you are welcome. IFS works till first condition which is met, and it's not necessary to repeat previous conditions with AND or by another way. Similar is with nested IF.
Subodh_Tiwari_sktneer
Silver Contributor
Oct 06, 2019
Sydwizz
You're welcome! Glad it worked as desired.

The issue was, if you have only one logical condition, you constructed it correctly in the formula but when you have two AND logical conditional to be evaluated, you need to use the function AND like below...
AND( B4>0.16, B4<0.33)
The above logical condition will be considered as one logical condition and if both are evaluated as True, then only the control passes to the True value you mention else it will pass to the False value.

So as per the above condition, it will check if B4 is greater than 0.16 AND B4 is less than 0.33, then only the formula will return 2 else it will skip to the False value which is another IF function and that will be evaluated next.

Hope that helps.

Forum Discussion

IFS function with multiple conditions per value

Resources