Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

You are in vain trying to apply the function of the IFS() for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.

=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")

or

=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)

There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case

Don't write "Thank you"

You are in vain trying to apply the function of the IFS() for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.

=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")

or

=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)

There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case

Update Other variants for writing the last solution:

=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})

Perhaps with this arrangement of values and actions the formula will be easier to edit.

Considering comment of esteemed colleague @robleyd, I bring also the variant of the formula using a set of nested IF().

=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))

You have to admit that the work of editing the formula very difficult in such a record. Don't write "Thank you"