IFS – select from multiple options
If there is something most of Excel users hate, then it is nesting of IF function. But now, from 2016, there is a new function making this multiple-choice selection much easier.
It is very helpful when you need to select from more options than 2.
Like here. There are employees and their salaries. We want to distinguish between very low salary (less than 1000 EUR), low salary (1000 – 2000), medium salary (2000 – 3000) and high salary (more then 3000).
This formula will do it:
- =IFS(B2<1000;”very low”;B2<2000;”low”;B2<3000;”medium”;B2>=3000;”high salary”)
Because:
- B2<1000 – the first condition to be evaluated
- “very low” – what should be shown when its true
- B2<2000 – se second condition to be evaluated if the first one wasn´t true
- “low” – what should be shown when the second condition is true
- …
Notes
- If more conditions are true, IFS shows the value for the first of them.
- I you need some “default” value, which should be shown when none of the conditions is true, you can write something like this to the end:
- =IFS(…;…;…;…;…;…;1;”default value”)
You can download the table here
You can download the table here