Forum Discussion
Creating an IF formula with a range of outputs
I'm trying to create an index scoring system where I look at a sum from other cells and I want to assign =>4 to populate 100 points, or =2 or 3 to populate 50 points or 1 to populate 10 points or 0 ...
There are many options. The first is nesting IF() but that gets ugly and hard to work with. Next is using IFS(). The format is IFS(condition, output, condition2, output2, ...) where it checks conditions in order and gives the output of the first condition that is TRUE. So in this case:
IFS(W6>= 4, 100, W6>=2, 50, W6>0, 10, W6=0, 0)
alternatively the last condition I recommend a TRUE as a default case:
IFS(W6>= 4, 100, W6>=2, 50, W6>0, 10, TRUE, 0)
Another option is to create a table of values and output and then use LOOKUP() or VLOOKUP() or the new and preferred XLOOKUP() to find the value accordingly. This option is helpful since it is easy to see and change the criteria and values since they are listed on the sheet itself but for 2 or 3 values above, might be an overkill.
IFS(W6>= 4, 100, W6>=2, 50, W6>0, 10, W6=0, 0)
alternatively the last condition I recommend a TRUE as a default case:
IFS(W6>= 4, 100, W6>=2, 50, W6>0, 10, TRUE, 0)
Another option is to create a table of values and output and then use LOOKUP() or VLOOKUP() or the new and preferred XLOOKUP() to find the value accordingly. This option is helpful since it is easy to see and change the criteria and values since they are listed on the sheet itself but for 2 or 3 values above, might be an overkill.