Ask Your Question

Need formula using ifs [closed]

asked 2017-09-07 22:52:57 +0200

dogluvver gravatar image

Have a range of numbers 1 - 60. I want to make a choice depending on a range of numbers i.e. If the number is between 1 and 10 then do THIS if the number is between 11and 20 do THAT if the number is between 21 and 40 do SOMETHING ELSE if the number is between 41 and 50 do XYZ if the number is between 51 and 60 do ABC

The THIS/THATetc names relate to formula.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-14 23:13:44.673811

2 Answers

Sort by » oldest newest most voted

answered 2017-09-08 08:19:20 +0200

JohnSUN gravatar image

updated 2017-09-08 13:02:55 +0200

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.



=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"

edit flag offensive delete link more


I wonder if the question meant using a number of nested IF statements ?

robleyd gravatar imagerobleyd ( 2017-09-08 10:19:17 +0200 )edit

WRT your updated multiple-IFs variant: (nerd mode on) for your brilliant *LOOKUP solutions to be equivalent to it, you should put them into IF(A1>60;NA();VLOOKUP(...)) ;-)

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-08 15:26:28 +0200 )edit

@Mike Kaganski You are absolutely right. But when I read "The THIS/THATetc names relate to formula", I thought that "ABC" may well turn out to be the same NA(). And why do we need so many NA() in a single formula? ;-)

JohnSUN gravatar imageJohnSUN ( 2017-09-08 15:47:13 +0200 )edit

answered 2017-09-08 13:50:05 +0200

dogluvver gravatar image

Thx - I had an ah-hah moment in the middle of the night and came up with the vlookup solution.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-09-07 22:52:57 +0200

Seen: 980 times

Last updated: Sep 08 '17